Question: History of ms sql queries
How to view request history (delete, update...)?
Answer:
the profiler is the slowest audit you can think of. Extended Events can be customized to suit your needs and it can also upload, etc., but any of these options requires resources.
Question: Query execution history
Tell me, is it possible to see somewhere in MS SQL who performed what queries in a specific database?
Answer: iluxa1810, this information is only partially available. Try a query like:
| T-SQL code | ||
|
||
For full history queries need to be logging or using utilities like SQL Profiler.
Question: Sql query to return previous values to the database
Hi all!
Please tell me how can I return the penultimate order statuses in all orders starting from a certain date in the database?
Maybe there is some kind of sql query or maybe some kind of script... I would be very grateful.
The programmer made a slight mistake when integrating with my warehouse and all orders changed their status to the one that was in the integration module settings and does not know how to return it back.
We didn’t notice right away and it was too late to restore the old copy of the database. There are already new orders in the store.
Answer: Poetiq,
InnoDB can store query history.
| Bash | ||
|
||
Roll up the latest backup and repeat the commands
Well, in general, before each integration you should make a backup, and even better, test it on a test server
Question: SQL query to search from multiple tables
Good afternoon. Please help me understand whether I can do this or whether the database needs to be redone or whether such a search is not possible to implement.
I am writing on delphi rad xe10.
I write information about the employee in the Employees table in one line, in size clothing is also one line, but the history that was given to him (many lines and the only difference is ID_rab).
I want to do a search
so that when asked by last name or tab number it would give me the following information:
About the worker from the first table in dbgrid1,
its sizes from the second table in dbgrid2
and the history related to this employee from Table 3 (there may be many rows that only differ by ID_rab) in dbgrid3
In the first table:
in the second:
| SQL | ||
|
||
in the third:
| SQL | ||
|
||
Make 3 ADOQuery with different queries at once?
I don’t know how to make it display information from another table that belongs to the first one
If it were possible to do this: I entered a person into the search, it found him and, using his ID_rab, it would find information in other tables and at the same time display it in 3 different dbgrids.
Answer:
Message from Rogzar
And if you completely delete all information about an employee, his size, history, as required, can this be done through one request?
If you enable cascade deletion on the Employees table, then deleting a row in it will delete data from the subordinate tables. Otherwise you will have to make several DELETE requests.
Added after 14 hours 31 minutes
Such code is vulnerable to attacks called. Queries with parameters should be used.
Question: One request - is it possible?
Good afternoon, ladies and gentlemen!
Tell me how to rationally solve the following problem...
There are three tables: products (there are really few of them, about 60-80 pieces), sales history and balances...
IN simplified the structure is as follows:
goods:
id
name
order_history:
id
s_date
good_id
price
balance:
id
s_date
order_id
count
All three tables are linked by ID (goods.id - order_history.good_id, order_history.id - balance.order_id).
The task is to get the total balance of all goods for any arbitrary date! That is, for each product I must get the closest date record from the balance sheet and sales history (from the balance sheet, respectively, the quantity, from the sales history - the price).
It’s clear that you can loop through all the products by ID and execute an SQL query in each iteration, but somehow... I don’t like this option! :) Is it possible to solve the problem gracefully - with one request?
I would be grateful for any ideas!
Answer: I don't see any contraindications. If you put the structure in order.
| Blunt! |
|---|
| Added some redundancy to the database |
Question: Nested query, help me combine several simple ones
Good afternoon I will be very grateful for your help)) I have been racking my brain for a long time.
A little history. There is a certain pool of requests with a URL related to them, the goal is to issue intersecting requests for a certain list of URLs, which is also determined by the request
And so there are several queries that are executed in order:
$sql2 = SELECT url FROM list WHERE `group` = "1" AND `proekt`= "1" GROUP BY url ORDER BY COUNT(*) DESC LIMIT 3 - screenshot
I select a list of URLs in the amount of 3 pieces and then collect them into a view for the request
$return=array();
$maxcount=mysql_query($sql2);
while($url = mysql_fetch_array($maxcount)) (
$return = $url;
}
foreach ($return as $url1) ( //collecting a string of URLs for Muscle from an array
if ($frag2==0) ( $urls = "`url`="."".$url1."""; $frag2=1; ) else $urls .= " OR `url` = "". $url1.""";
}
In this case $urls = `url` = "http://www.MosDance.com/" OR `url`="http://raisky.com/" etc.
I display a list of keys related to these URLs
select `key` from (SELECT `key` , COUNT(*) AS total FROM list WHERE `group` = "1" AND `proekt`= "1" AND (".$urls.") GROUP BY `key` ORDER BY `total` DESC) as t2 WHERE t2.total >=3
Actually the main question is how to reduce this matter? How to correctly assemble the URL, so that you can get rid of the php crutch script that collects the URL in the required order and do it all, for example, with one query in the database? in general, how can I get the final result from one query smarter than my crutches?
Answer:
Good day!
Never write COUNT(*). A better option would be COUNT(`id`).
Also, don't write RAND(*). It’s better to write it differently, of course: comments have functions in them, and this is the case.
All of the above methods for selecting records from the database heavily load the mysql server. Unless, of course, you have 30 thousand records in the database, it won’t be so painful.
Regarding the request:
Dig towards INNER JOIN. Or send me a dump of your table, I will help you if you tell me the problem in more detail, since what I read is too lazy to delve into all the fuss =)
-----
- programming lessons here. Interesting features there too =)
Question: Query from two tables using several criteria
Good evening!
Please help me make the following request:
1. There is a table of transactions with fields: name of currency, date, type of exchange rate and other fields
2. There is a table with the history of exchange rates with fields: currency name, date, type of exchange rate, exchange rate.
The request must be generated based on all fields and records of the first table, and the exchange rate from the second table. Moreover, there must be a coincidence of all 3 selection criteria: name of currency, date and type of exchange rate.
Thank you in advance!
Answer:
You will be more comfortable in the query designer.
I figured out how to make inner joy via SQL, at first glance it’s simple. But there is a question:
1. In the transaction table there is a field "Transaction type"
2. The query I make first pulls for each table entry the "Currency Rate Type" transactions from the "Transaction Types" table
3. The request pulls out from the table with courses according to the “Type of course” criterion obtained in step 2.
In other words: first, 2 tables are merged (transactions and transaction types"), and then due to general information data from the "Currency Rates" table is added to them.
Question: Find out on which table record the query fails with an error
I came across a situation where a query to a fact table returns an error, but I can’t catch entries in the directory that lead to an error (the question is about dates)
The query itself returns all records from the fact table, plus a calculated field from the directory. If the directory were populated correctly, the subquery would return one value per fact table record. But in fact, there are errors in the entries in the directory that I cannot catch.
Is it possible to somehow view which fact table record is causing the error?
Answer: Not funny. For it has long been (literally):
Well, as soon as the delusion detector went off, he immediately came running... What's wrong?
Before heroically writing super-queries (for all occasions), you need to turn on your brain - well, at least for a couple of minutes...
Well, personally, your “suffering” was enough for a crazy request, the place of which is the trash can.
And you got a “slipper” only for its semantics and syntax...
Good idea!
And as soon as you wash it, take it off the shelf (well, at least!) - and “study, study and study” (c). There's definitely something about it sharing GROUP BY and HAVING...
Question: Query profiling programs
Greetings!
Tell me who uses what (programs, IDEs, etc.) to parse queries; in particular, I am interested in query analysis tools for their optimization. Where you can see the query plan: query indexes, Full scan, etc.
11) | DATE | DATE | driver_name | VARCHAR(100) | percent | INT(11) | sum_recieved_from_client | DECIMAL (9 , 2 ) | sum_tariff_for_driver | DECIMAL (9 , 2 ) | summa_for_paid | DECIMAL (9 , 2 ) | paid_status | tinyint(1) | percent_company | INT(11)The "paid_status" field stores the payment status. Needed in a request like:
get all data. It should be like this: if “paid_status = 1”, then instead of the number that is in the “summa_for_payed” field you will always receive “0”.
If I add “and paid_status not like “1”” to the request, it turns out that the output of this line will not be received at all, but I need to get everything, only with the field “summa_for_payed” already changed depending on the state of “paid_status”
Added after 25 minutes
I’ll add, it is also necessary that during a request with the command sum(summa_for_payed) for a certain date, get the amount, taking into account the value of the “paid_status” field, or rather, that where “paid_status = 1”, then the “summa_for_payed” fields are considered in the amount as "0" if "paid_status = 1"
Answer: Yes, I thought so, but the problem is this: if I reset the line, and if suddenly the wrong user was selected by mistake, then I won’t be able to return it back, since his old debt was erased, so I thought just using this indicator and determining payment, and if necessary, return it back again, seeing his amount of debt. Well, in general, it would be better for history to see his debt, which he paid off.
Added after 34 minutes
Dear gurus.
I found something that can be done like this
| SQL | ||
|
||
I made several requests, it seems to be what I need, but can you tell me if there are any pitfalls in such a request, is this what I need? Should I do this or look for other solutions? Triggers also come to mind. maybe something with them? What do you advise. I also thought about changing the values, if 0 then it’s paid, if 1 then it’s not, and then in the selection, multiply the number by the indicator, and if it’s zero, then I’ll just get zero. if there is one, then I will get the number itself. just unnecessary calculations... What is your opinion?
Event logs are the first and simplest tool for determining system status and identifying errors. There are four main logs in MySQL:
- Error Log— standard error log that is collected while the server is running (including start and stop);
- Binary Log— a log of all database modification commands, needed for replication and backups;
- General Query Log— main query log;
- Slow Query Log— log of slow requests.
Error log
This log contains all errors that occurred while the server was running, including critical errors, as well as stopping, turning on the server and warnings. This is where you should start in the event of a system failure. By default, all errors are output to the console (stderr), you can also log errors to syslog (default on Debian) or a separate log file:
Log_error=/var/log/mysql/mysql_error.log
# Errors will be written to mysql_error.log
We recommend keeping this log enabled to quickly identify errors. And to understand what this or that error means, MySQL has the perror utility:
Shell> perror 13 64 OS error code 13: Permission denied OS error code 64: Machine is not on the network
# Explains the meaning of error codes
Binary (aka binary) log
All database modification commands are recorded in the binary log, useful for replication and recovery.
It turns on like this:
Log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 5 max_binlog_size = 500M
# Specifies the location, lifetime, and maximum file size
Please note that if you are not going to scale the system and implement fault tolerance, then it is better not to enable the binary log. It is resource intensive and reduces system performance.
Request log
This log contains all received SQL queries and information about client connections. Can be useful for index analysis and optimization, as well as identifying erroneous queries:
General_log_file = /var/log/mysql/mysql.log general_log = 1
# Includes the log and indicates the file location
You can also enable/disable it while the MySQL server is running:
SET GLOBAL general_log = "ON"; SET GLOBAL general_log = "OFF";
# You don't need to restart the server to use it
Slow request log
The log is useful for identifying slow, that is, inefficient queries. Read more in this article.
Viewing logs
To view logs on Debian (Ubuntu) you need to run:
# Error log tail -f /var/log/syslog #Query log tail -f /var/log/mysql/mysql.log # Log slow requests tail -f /var/log/mysql/mysql-slow.log
# If the logs are not specified separately, they are located in /var/lib/mysql
Log rotation
Don’t forget to compress (archive, rotate) log files so that they take up less space on the server. To do this, use the utility logrotate by editing the configuration file /etc/logrotate.d/mysql-server:
# - I put everything in one block and added sharedscripts, so that mysql gets # flush-logs"d only once. # Else the binary logs would automatically increase by n times every day. # - The error log is obsolete, messages go to syslog now./var/log/mysql.log /var/log/mysql/mysql.log /var/log/mysql/mysql-slow.log( daily rotate 7 missingok create 640 mysql adm compress sharedscripts postrotate test -x /usr/bin/mysqladmin || exit 0 # If this fails, check debian.conf! MYADMIN="/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf" if [ -z "`$MYADMIN ping 2>/dev/null`" ]; then # Really no mysqld or rather a missing debian-sys-maint user? # If this occurs and is not an error please report a bug. #if ps cax | grep -q mysqld; then if killall -q -s0 -umysql mysqld; then exit 1 fi else $MYADMIN flush-logs fi endscript )
# Compresses and archives necessary logs, cleans files
DDL Log
MySQL also maintains a data language log. It collects data from operations like DROP_TABLE and ALTER_TABLE. The log is used to recover from failures that occurred during such operations. DDL Log is a binary file and is not intended to be read by the user, so do not modify or delete it.
The most important
Always turn on the error log, use the query log to check the application's connection to the database, check queries and operation. The log of slow queries is useful for optimizing MySQL performance.
And in the standard package you can do a lot of things with it. But be that as it may, it has a number of useful features that can be enabled separately. In this article we will reveal additional features PhpMyAdmin, and let's see how they can be useful to us.
Settings
Before you begin, make sure PhpMyAdmin is up and running. If you log in, you may notice a message like this:
The phpMyAdmin configuration storage is not completely configured, some extended features have been deactivated. To find out why click here.
(phpMyAdmin configuration store is not configured properly, some features are disabled. To find out why, click here)
$cfg["Servers"][$i]["controluser"] = "pma"; $cfg["Servers"][$i]["controlpass"] = "pmapass"; $cfg["Servers"][$i]["pmadb"] = "phpmyadmin"; $cfg["Servers"][$i]["bookmarktable"] = "pma__bookmark"; $cfg["Servers"][$i]["relation"] = "pma__relation"; $cfg["Servers"][$i]["table_info"] = "pma__table_info"; $cfg["Servers"][$i]["pdf_pages"] = "pma__pdf_pages"; $cfg["Servers"][$i]["table_coords"] = "pma__table_coords"; $cfg["Servers"][$i]["column_info"] = "pma__column_info"; $cfg["Servers"][$i]["history"] = "pma__history"; $cfg["Servers"][$i]["recent"] = "pma__recent"; $cfg["Servers"][$i]["table_uiprefs"] = "pma__table_uiprefs"; $cfg["Servers"][$i]["users"] = "pma__users"; $cfg["Servers"][$i]["usergroups"] = "pma__usergroups"; $cfg["Servers"][$i]["navigationhiding"] = "pma__navigationhiding"; $cfg["Servers"][$i]["tracking"] = "pma__tracking"; $cfg["Servers"][$i]["userconfig"] = "pma__userconfig"; $cfg["Servers"][$i]["designer_coords"] = "pma__designer_coords"; $cfg["Servers"][$i]["favorite"] = 'pma__favorite'; $cfg["Servers"][$i]["savedsearches"] = "pma__savedsearches";
Change the username, password and database name at your own discretion. The remaining configuration values are table names. Leaving them blank will disable their corresponding features. But in general, I would recommend leaving these names as they are. Depending on the version of PhpMyAdmin, not all values will be available by default. In this case, I used PhpMyAdmin version 4.2.x.
Once you are done with the config, you will need to create the database. Create a database with the name you specified in your configuration file. Next, look for a file called create_tables.sql on your machine. More likely, this file will be located in the root directory of the installed PhpMyAdmin, or in the scripts subdirectory. If you are on Linux, the location will be /usr/share/phpMyAdmin/examples/ or /usr/share/doc/phpmyadmin/scripts/ . Run this file within the created database. If you changed the table names in the above config, do not forget to change them in the database as well.
GRANT USAGE ON mysql.* TO "pma"@"localhost" IDENTIFIED BY "pmapass"; GRANT SELECT (Host, User, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_ priv) ON mysql.user TO "pma"@"localhost"; GRANT SELECT ON mysql.db TO "pma"@"localhost"; GRANT SELECT ON mysql.host TO "pma"@"localhost"; GRANT SELECT (Host, Db, User, Table_name, Table_priv, Column_priv) ON mysql.tables_priv TO "pma"@"localhost"; GRANT SELECT, INSERT, UPDATE, DELETE ON phpmyadmin.* TO "pma"@"localhost";
After logging out of PhpMyAdmin and logging in again, we should see that the warning has disappeared. If it's not missing, click on the link to find out what's missing.
Let's look at additional options.
Bookmarks
By enabling the bookmark extension, you will be able to bookmark queries. When you create a request, you can add it to your bookmarks. Also, if the request has already been launched, you can bookmark it by scrolling to the bottom of the page and filling out the form.
When you go to the SQL tab, at the bottom you will see a panel where you can select a query from the bookmarks. Select the one you need and indicate what you want to do with it: execute, view, or delete.
Connections
The relation option is probably the best thing I've ever used. If you use foreign keys, then such keys will become clickable. When you click on a foreign key, you will be taken to another table: the record referenced by the foreign key.
Also, when adding or editing a foreign key, instead of a text input field, you will be presented with a list of all possible options.
Table information
Of course, seeing a list with all possible values for a foreign key is good. But it would be even better if we were shown a specific column instead of ID. To achieve this, you need to enable able_info . Go to the main table, go to the “Structure” tab, and click on the link to view the relationship. If you scroll to the bottom of the page, you will see which column will appear for that key.
If I create a new record with a foreign key, then next to the primary key value I will see the value from the column I specified. Now there is no need to remember the ID.
PDF pages
Relationships between tables can be exported to PDF. To do this, you need to activate pdf_pages, table_coords and relation in the config. Next step open the database you want to export to PDF. Click on the “Operations” tab. Next, click on the “Edit or export wiring diagram” link. Fill in all fields and click “Submit”. If you have filled out the form, you will have PDF file with all the necessary data.
Column Information
With the column info option you can add comments to column names. This can be useful when you want to convey to other people what the column is and what data it stores. You can do this by changing the column data. You may notice that a new comment has been added to the column. If you enter a comment, it will appear directly below the column name.
Transformations can be applied to columns in the same way. Using transforms, you can make a file name into a hyperlink that will open the specified file.
Story
If you enable history , all requests made through PhpMyAdmin will be saved in your history until you log out or close the browser. The history can be viewed by calling the “Query” dialog box and clicking the “SQL History” button
Recent
In the navigation bar, you may notice a “Recent” button that contains a drop-down list of tables that you have recently viewed. This list is displayed by default. But if you want to store it permanently between sessions, you can enable the recent option in the config, and your browsing history will be saved in the database.
Table display settings
While viewing tables, you can change the order of columns by moving them. These changes are visible only to you. The actual order of the columns does not change. In addition, you can also sort the columns in ascending and descending order. Such changes are stored within the session, and when logged out they will be reset and replaced with the default settings. However, if you want tables to be laid out “your way” all the time, then enable the table_uiprefs option.
Users and user groups
You may not want to display all features for all users. In this case, you can divide them into groups on the “Users” tab. Create new group, and determine what users in this group can see. You can then add users to this group, and you can be sure that they will only see the menu items that you allow them to see. This way you can give different users access to different system features, as well as hide unused options.
Hide navigation
With navigationhiding you can hide some tables in the navigation bar. Clicking on the light bulb icon next to the table name will hide it. You can also make it visible again by clicking on the light bulb icon again. This way you will relieve the navigation bar of unused tables.
Tracking
Using this feature, you can track all the changes that were made to specific tables using PhpMyAdmin. By going to the table and opening the “Tracking” tab, you can create a version of the current table. Now all changes that were made to this table using PhpMyAdmin will be saved. In the same way, you can easily determine what changes have been made to the table since the last version was created.
Custom Settings
All user settings are stored in your browser by default. This means that if you decide to use a different browser, or clear your browser cache, all your settings will be lost. By activating userconfig you will save your settings in the database permanently.
Database designer coordinates
By enabling designer_coords, you can view the relationships between your tables in database designer view. By going to the database and clicking on the “Designer” tab, you will see something similar to the image below, which shows the relationships between the tables. At the same time, you can visually manage the relationships between tables using this window.
Favorites
By enabling the favorite option, you may notice star icons appear next to table names in database browsing mode. To add a table to your favorites, just click on the star. In the navigation bar, you will see a “Favorites” drop-down list. Tables you've added to your favorites will appear in this list.
Saving search parameters
By opening the database and going to the Query tab, you can set up advanced searches. Search options can be bookmarked. Note that these are not the SQL query bookmarks discussed earlier. With this option you can save your search parameters and use them later.
Conclusion
The features listed are easy to enable and can really help you work with databases through PhpMyAdmin. I especially like the ability to drill down on foreign keys so that I can see related records. I hope you enable these features and they will be useful in your daily work.
Loading...
