error "--secure-file-priv option" when save selection to csv

I am using MySQL Server 8.0 on Windows 10. I tried to load data from csv file using the following commands:

LOAD DATA INFILE 'C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\World_cup_dataset.csv'
INTO TABLE trial
FIELDS TERMINATED BY ',';

When I run above commands, I got

  "ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement"

I tried many methods to solve the problem. Finally, I changed the '\' characters in the file path to '/' characters.

I mean my new file path became C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/World_cup_dataset.csv and my problem were fixed. If you look at my.ini file located in the MySQL Server folder, the path of secure-file-priv is "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads". So, this solution makes sense.


Storing prices with price history and different specificity (global, user group, single user) in MySQL database

There are a couple of ways you could separate the current values from the historic ones: you could simply include a boolean field that is true for the latest price and filter on that, or you could keep current prices in a separate table. Both options involve a little extra work to maintain integrity but would make a query for current prices more efficient. This doesn't remove the need for the sub-query to pick the first row that matches the user/group/NULL condition, though fewer rows will be scanned in processing both the inner and outer queries.

The following might be more efficient as a straight query if only the current prices are in the base table (add the is_latest=true check or your current date filter to the where and on clauses if you keep the historic values in the same table):

SELECT CASE WHEN usermatch.id IS NOT NULL THEN usermatch.field1 WHEN groupmatch.id IS NOT NULL THEN groupmatch.field1 ELSE nullmatch.field1 END AS field1
     , CASE WHEN usermatch.id IS NOT NULL THEN usermatch.field2 WHEN groupmatch.id IS NOT NULL THEN groupmatch.field2 ELSE nullmatch.field2 END AS field2
     (... and so on ...)
     , CASE WHEN usermatch.id IS NOT NULL THEN usermatch.fieldN WHEN groupmatch.id IS NOT NULL THEN groupmatch.fieldN ELSE nullmatch.fieldN END AS fieldN
FROM   base_prices AS nullmatch
LEFT OUTER JOIN
       base_prices AS groupmatch ON groupmatch.id=nullmatch.id AND nullmatch.referent_id = @GroupIDHere
LEFT OUTER JOIN
       base_prices AS usermatch ON usermatch.id=nullmatch.id AND nullmatch.referent_id = @UserIDHere
WHERE nullmatch.referent_id IS NULL

This will only work if you always have a row for each product where referent_id is null, otherwise you could try:

SELECT CASE WHEN usermatch.id IS NOT NULL THEN usermatch.field1 WHEN groupmatch.id IS NOT NULL THEN groupmatch.field1 ELSE nullmatch.field1 END AS field1
     , CASE WHEN usermatch.id IS NOT NULL THEN usermatch.field2 WHEN groupmatch.id IS NOT NULL THEN groupmatch.field2 ELSE nullmatch.field2 END AS field2
     (... and so on ...)
     , CASE WHEN usermatch.id IS NOT NULL THEN usermatch.fieldN WHEN groupmatch.id IS NOT NULL THEN groupmatch.fieldN ELSE nullmatch.fieldN END AS fieldN
FROM   users
CROSS JOIN
       base_prices AS nullmatch
LEFT OUTER JOIN
       base_prices AS groupmatch ON groupmatch.id=nullmatch.id AND nullmatch.referent_id = users.groupid
LEFT OUTER JOIN
       base_prices AS usermatch ON usermatch.id=nullmatch.id AND nullmatch.referent_id = users.id
WHERE users.id = @UserIDHere
AND   nullmatch.referent_id IS NULL

In both cases be careful using this construct in a view because further filtering by the fields brought out via those case statements will not be able to use any indexes you may have defined.

Obviously the case statement per field is a bit of faf but it should stop the query runner searching the table once for every row returned as it might with the sub-query arrangement.

In other databases you might be able to use windowing functions (like ROW_NUMBER) to do this more conveniently and efficiently, but mySQL does not support those to my knowledge.


How else to troubleshoot LCK_M_U from SQL Server?

Do you know exactly what the INSERT statement is and what line of EF code generates it? Just wrap whatever calls that around some logging code and log all the SQL to a text file (preferably using log4net, nlog, ETW etc.

Just wrap the code that calls the insert code with a call to set and unset the delegate described here.

e.g.

 ctx.Database.Log = (dbLog => log.Debug(dbLog));
// Do the insert
ctx.Database.Log = null;

Honestly, if you can deal with a slight slowdown in performance, and have the disk space just log everything. I log all the SQL in my Always Encrypted Sample app.

Assuming your INSERTS are wrapped around transactions, you should be able to see that instantly with this. Make sure your logging has timestamps so you can see how long the transaction remains open for.


How to perform JOIN and only use single record for each instance on 2nd table

For future posts, it is better to provide CREATE TABLE statements and INSERT statements. It is also beneficial to add an expected result set. What I guess you are trying to do is to pair Product_Plan_Creation_Date and Product_Plan_Delivery_Date. You can create an ordering number with a WINDOW function:

SELECT Product_Plan_ID, Product_Plan_Creation_Date
     , ROW_NUMBER() OVER (PARTITION BY Product_Plan_ID
                          ORDER BY Product_Plan_Creation_Date) AS RN
FROM Products

and similar for Product_Delivery:

SELECT Product_Plan_ID, Product_Plan_Delivery_Date
     , ROW_NUMBER() OVER (PARTITION BY Product_Plan_ID
                          ORDER BY Product_Plan_Delivery_Date) AS RN
FROM Product_Delivery

Now you can use the ordering number to join these two derived tables:

SELECT A.Product_Plan_ID, A.Product_Plan_Creation_Date
     , B.Product_Plan_Delivery_Date
FROM (
    SELECT Product_Plan_ID, Product_Plan_Creation_Date
         , ROW_NUMBER() OVER (PARTITION BY Product_Plan_ID
                              ORDER BY Product_Plan_Creation_Date) AS RN
    FROM Products
) AS A
JOIN (
    SELECT Product_Plan_ID, Product_Plan_Delivery_Date
         , ROW_NUMBER() OVER (PARTITION BY Product_Plan_ID
                          ORDER BY Product_Plan_Delivery_Date) AS RN
    FROM Product_Delivery
) AS B
    ON A.Product_Plan_ID = B.Product_Plan_ID
    AND A.RN = B.RN


Access "couldn't use ... file already in use"

From what I have read this problem can happen on a network if computers are unable to create/modify the .ldb file. The .mdb file is on a Novell network drive that all the computers have read/write/create permissions on, and everything works fine until the computers stop being able to open the file.

What you're doing is very dangerous. Database engines handle MVCC. File I/O is not sufficient. That said, Microsoft Access has a method to get around this called Shared Access Mode. I wouldn't suggest using this but it should get around the error problem.

Make sure that Access is set to open in shared mode on all of the users' computers. This is the default setting, but you should check to be sure — if a user opens the database in exclusive mode, it will interfere with data availability. Complete the following procedure on each computer:

Start Access and under File, click Options. Note, if you're using Access 2007, click the Microsoft Office Button and then click Access Options.
In the Access Options box, click Client Settings. Note, if you're using Access 2007, click Advanced.
In the Advanced section, under Default open mode, select Shared, click OK, and exit Access.

As a side note, it's my bread and butter migrating people away from Microsoft Access, Visual Basic, and Perl. Any decent consultant salivates at the mouth when they read about this for the implications to provide value. Shared Access Mode should not be used without extensive backup solutions. Things will go wrong, and when they do expect downtown. I have a few clients on this and we sync their stuff up to AWS every 15 minutes.
Note, I see you're on Access 97. I'm not sure when they introduced Shared Access mode, but you'll need to upgrade if that option isn't there. I would really not want to work around that, it's not in your best interests if you're advising them, or theirs.


Can I merge duplicate results into a single result? (SQL)

You can do it using the row_number() window function. What (I think that) you want is to PARTITION your data into subgroups, where each partition has a common (Time, Course). Then, for each partition, you want to output Time, Course only on the first row of each partition. In your example, all rows become one single partition; but this could not be the case.

The SQL query you would use would start with your original one, and add a few tricks to "hide" the unwanted output:

; WITH e AS
(
    /* We start with a (slightly simplified) version of the original query */
    SELECT
        CONCAT(CONVERT(VARCHAR(5),Event_Start_Time, 108), ' - ', CONVERT(VARCHAR(5),Event_End_Time, 108)
        ) AS Time,
        CASE WHEN Course_Code IS NULL
            THEN Course_Title
            ELSE Course_Code
        END AS Course,
        CASE WHEN User_Last_Name IN ('Faculty', 'Students', 'Study') OR Course_Title = 'Meeting'
            THEN NULL
            ELSE CONCAT(substring(User_First_Name, 1, 1), '. ', User_Last_Name)
        END AS Instructor,
        Room_Name AS Room,
        Event_Start_Time, Event_End_Time, User_Last_Name /* We will still need these columns at output to sort by */
    FROM
        Events
        JOIN Courses ON Courses.Course_ID = Events.Course_ID
        JOIN Users ON Users.User_ID = Events.User_ID
        JOIN Rooms ON Rooms.Room_ID = Events.Room_ID
    WHERE
        Events.Event_Date = (SELECT CAST(GETDATE()AS Date))
)
/* We use window functions, and partition by (Event_Start_Time, Event_End_Time), order by User_Last_Name. We will output only Time and Course on First Row */
SELECT
    case when (row_number()
          over (PARTITION BY Event_Start_Time, Event_End_Time
               ORDER BY User_Last_Name) ) = 1 then Time   end AS Time,
    case when (row_number()
          over (PARTITION BY Event_Start_Time, Event_End_Time
                ORDER BY User_Last_Name) ) = 1 then Course end AS Course,
    Instructor, Room
FROM
    e
ORDER BY
    Event_Start_Time, Event_End_Time, User_Last_Name;

You will get:

 Time          | Course   | Instructor   | Room
 :------------ | :------- | :----------- | :---
 08:00 - 11:30 | NURS3405 | F. Lastname1 | LabA
 null          | null     | F. Lastname2 | LabB
 null          | null     | F. Lastname3 | LabC
 null          | null     | F. Lastname4 | LabD

You can check everything in this DBFiddle.

NOTES:

You can remove a few SELECT from your original query. They are not needed.
Your way of "shortening" times seems not to work on all cases. I have used a different approach taken from Stack Overflow's "SQL Server 2005 DateTime (return only hh:mm)".
I am not sure at all my assumptions about your tables are correct (you are recommended to post the structure of your tables (or the relevant part of it) when posting questions). Advise, otherwise.
We use the fact that CASE WHEN cond THEN a END will return NULL when cond is not met. If you need an empty string instead of NULL, you must change the expressions to CASE WHEN cond THEN a ELSE '' END.
Seriously consider the comment from @sp_BlitzErik: this should most probably be done at the presentation layer, not the database layer. It just happens, sometimes, that you can control your database layer better than the presentation one. If that's the case, this is (one possible) alternative. This solution is, in fact, a grouping set in disguise, with a little bit more of flexibility, and without the OLAP cubes background.

References:

Window Function Examples for SQL Server
OVER clause
row_number() function

Data model used for the example:

Tables, PK and FK

 CREATE TABLE Rooms
 (
    Room_ID integer PRIMARY KEY,
    Room_Name varchar(50)
 ) ;

 CREATE TABLE Courses
 (
    Course_ID integer PRIMARY KEY,
    Course_Code varchar(50),
    Course_Title varchar(50)
 ) ;

 CREATE TABLE Users
 (
    User_ID integer PRIMARY KEY,
    User_First_Name varchar(50),
    User_Last_Name varchar(50)
 ) ;

 CREATE TABLE Events
 (
    Event_ID integer PRIMARY KEY,
    Course_ID integer REFERENCES Courses(Course_ID),
    Room_ID integer REFERENCES Rooms(Room_ID),
    -- User_ID is most probably the "organizer"/"lecturer", not the "attendant"
    User_ID integer REFERENCES Users(User_ID),
    Event_Date date,
    Event_Start_Time time,
    Event_End_Time time
 ) ;

Data

 INSERT INTO Users
     (User_ID, User_First_Name, User_Last_name)
 VALUES
     (  1, 'FirstName1', 'Lastname1'),
     (  2, 'FirstName1', 'Lastname2'),
     (  3, 'FirstName1', 'Lastname3'),
     (  4, 'FirstName1', 'Lastname4') ;

 INSERT INTO Courses
     (Course_ID, Course_Code, Course_Title)
 VALUES
     (100, 'NURS3405', 'Title NURS3405') ;

 INSERT INTO Rooms
     (Room_ID, Room_Name)
 VALUES
     (201, 'LabA'),
     (202, 'LabB'),
     (203, 'LabC'),
     (204, 'LabD') ;

 INSERT INTO Events
     (Event_ID, Course_ID, Room_ID, User_ID, Event_Date, Event_Start_Time, Event_End_Time)
 VALUES
      (300, 100, 201, 1, GetDate(), '08:00', '11:30'),
      (301, 100, 202, 2, GetDate(), '08:00', '11:30'),
      (302, 100, 203, 3, GetDate(), '08:00', '11:30'),
      (303, 100, 204, 4, GetDate(), '08:00', '11:30') ;


How to create a backup schema script including System Tables in Sql Server 2008 R2?

Creating scripts of objects usually isn't the best backup method.

There shouldn't be any actual tables listed under Tables > System Tables.  Those should all be catalog views unless you've hacked the system objects to put one of yours tables in there.


How to use Italian locale for Postgres messages

First of all, this is an error message from PostgreSQL, which is not the same as psql (the command-line client). The SET command sets run-time parameters of the Postgres server, which is completely independent from the client (psql in this case).

Either way, it works for me. Testing the standard distribution of Postgres 9.4.1 on a Windows client set to a German locale (German_Germany.1252):

test=# SET LC_MESSAGES TO 'it_IT.UTF-8';
SET
test=# SELECT 1/0;
ERRORE:  divisione per zero

test=# SET lc_messages  = 'C';
SET
test=# SELECT 1/0;
ERROR:  division by zero

test=# RESET lc_messages;
RESET
test=# SELECT 1/0;
FEHLER:  Division durch Null

The same works on a Debian machine (where the respective locales are available!). And I can reproduce the same from any other client (like pgAdmin).

Maybe you are not running both commands in the same session?
Either way, Postgres uses locale support from the underlying OS. Per documentation:

  What locales are available on your system under what names depends on
  what was provided by the operating system vendor and what was
  installed. On most Unix systems, the command locale -a will provide a
  list of available locales. Windows uses more verbose locale names,
  such as German_Germany or Swedish_Sweden.1252, but the principles are
  the same.

And natural language support (--enable-nls) must be enabled at compile time which is the default and is the case for standard distributions. Details in the manual here.
Are you running a non-standard version? Compiled it yourself maybe?


What are the compression options for storing large amounts of text in Postgres?

By default Postgres automatically compresses everything TEXT. It uses a simple lzcompress algorythm:

https://www.postgresql.org/docs/9.3/storage-toast.html

There is a plugin that will probably evolve to LZ4 compression support for TEXT:

https://github.com/zilder/pg_lz4

There is a FDW that also support compression:

https://www.citusdata.com/blog/2014/04/03/columnar-store-for-analytics/


Can I add "Included Columns" to an index without affecting performance?

Why would you do anything through the SSMS GUI???

Try doing it as a pro, by a script. Your best option is to create the index online with drop existing, changing the definition to match the new desired schema in the process:

create index ... on ... include ... with (online = on, drop_existing = on);

This, obviously, is subject to the usual restrictions for ONLINE operations, and will cause the normal overhead caused by an ONLINE index create/rebuild operation. But the good news is that the operation will be online, will not block existing queries and the old index will be available for queries while the new one is being built. There will be a query drain stage at the end when all queries will block while the indexes are being swapped, but that should be a very fast operation (as long as there are not long running user transactions blocking the swap itself).


Filter predicates executing before Access predicates

The order of steps displayed in an execution plan is not the order of steps in which they are executed.

The database first accesses the index called NAME_OF_THE_INDEX using these predicates:

2 - access("TABLE_NAME"."column1">=TO_NUMBER(:CUTOFF) AND "TABLE_NAME"."column1" IS NOT NULL)

Then the database accesses the table called TABLE_NAME using the rowid from index NAME_OF_THE_INDEX  using these predicates:

1 - filter("TABLE_NAME"."column3"=:column_value AND TRUNC(INTERNAL_FUNCTION("TABLE_NAME"."column2"))=TRUNC(SYSDATE@!-1))

So it works as expected, and uses the access predicates first, and the filter predicates after that.

Unfortunately I could not find a good example with explanation in the official documentation, but I can recommend something better. To have a better understanding of execution plans, read these series (there are 14 parts):

https://www.red-gate.com/simple-talk/sql/oracle/execution-plans-part-1-finding-plans/


Should one always VACUUM ANALYZE before REINDEXing in PostgreSQL 8.4?

If the CREATE INDEX statement sees that another session holds an active snapshot that might still be interested in the deleted records, then it includes those deleted records into the new index.

Similarly, if a REINDEX sees that another session holds an active snapshot that might still be interested in the deleted records, then it includes those deleted records into the new index.

If a VACUUM sees that another session holds an active snapshot that might still be interested in the deleted records, then it keeps those records in the table. And then the REINDEX or CREATE INDEX also need to carry them into the new index, as long a the snapshot still exists.

Once there or no longer any snapshots that could possible see the deleted rows, then the VACUUM may remove them from the table.  But a CREATE INDEX or REINDEX could also just not carry them over into the new index, whether VACUUM had gotten around to removing them from the able or not.

So in your scenario, the role of the VACUUM between the initial CREATE INDEX and REINDEX is probably just to take up time, during which time your long-running transaction hopefully goes away on its own and drops the interfering snapshot.


Bulk change Agent Jobs' definition to "Quit the job reporting failure" from "Go to the next step" On Failure

Use Script to update Job steps In Bulk

USE MASTER
GO
SET QUOTED_IDENTIFIER ON
GO

    DECLARE  @job_id VARCHAR(1000),
             @Step_id INT

    DECLARE JobList CURSOR
            FOR

            SELECT  jb.job_ID,
                    jstp.step_id
            FROM    msdb.dbo.sysjobs jb
                    INNER JOIN msdb.dbo.sysjobsteps jstp ON jb.job_id = jstp.job_id
            WHERE   name = 'Jobname'
            --AND Step_name <>'Step Name'     Add Step name to filter
            ORDER BY jb.job_ID, jstp.step_id

    OPEN JobList
        FETCH NEXT FROM JobList INTO @job_id,@Step_id
        WHILE @@FETCH_STATUS = 0
            BEGIN

                     -- Execute SP sp_update_jobstep to update Job Steps and pass 2(Quit with failure) as value to parameter @on_fail_action
                    EXEC msdb.dbo.sp_update_jobstep @job_id = @job_id, @step_id = @Step_id, @on_fail_action = 2 ---- 2 Quit with failure.

                FETCH NEXT FROM JobList INTO @job_id,@Step_id
           END
        CLOSE JobList
        DEALLOCATE JobList

Note: Make sure all steps need to be updated on failure as "Quit the job reporting failure" otherwise filter them by adding in WHERE Clause

Reference Link : https://msdn.microsoft.com/en-us/library/ms189827.aspx


Moving a large table to another filegroup

Yes your Foreign Key constraints (References) will work once you move the clustered index to a different filegroup.
You can leave the non-clustered indexes in the old file group (in my personal opinion it has some advantage to have clustered and non clustered index  in different file group especially if the underlying disks are different and write heavy workload)
Remember when you create a non-clustered index by default it goes to the same file group where your clustered index is. If you want non-clustered indexes in a separate file group you need to explicitly mention that. Here is a good example of this.


I don't think there is a problem using the word commit and personally I don't see a better wording for the concept.

Most database systems do not support nested transactions, the only one I could find is BerkelyDB.

In their documentation on nested transactions they are using commit as the term.

  Committing a nested transaction has no effect on the state of the
  parent transaction.

In the book Concepts and Applications of Multilevel Transactions and Open Nested Transactions (1992)  they seem to be using the same wording.

  subtransactions can be made persistent independently of their commit
  state


NULL value causes blank row in SELECT results for text concatenation

Use COALESCE function to return the current value or an empty string.

SELECT '  (' || obj_id || ', ''' || coalesce(obj_type, '') || '''),'
FROM   objects;

| ?column?        |
| :-------------- |
|   (1, 'val 1'), |
|   (2, ''),      |
|   (3, 'val 3'), |
|   (4, 'val 4'), |

db<>fiddle here


How to delete records with FK constraint using Delete Trigger

The problem you originally had was that you were not referencing the deleted pseudo-table. You were essentially saying "Oh, delete subcategory 5? Ok, delete all products that have a subcategory!"
I think you want this in your trigger:
DELETE p
  FROM dbo.DimProduct AS p
  INNER JOIN deleted AS d
  ON p.ProductSubcategoryKey = d.ProductSubcategoryKey;

I still don't believe you want to delete every product that belongs to a subcategory you are removing, but hopefully this is just an exercise and not real business logic you want to implement.


Can I be notified of a rollback in my DDL Trigger?

Don't use DDL triggers. Use Event Notifications instead. Event Notifications convey the very same data as DDL triggers and occur on the very same events, but they are asynchronous and loosely coupled. You get notified only after the original DDL committed. If the DDL rolls back, you don't get any notification. It seems to be exactly what you need.


You might want to have a look at How to transfer logins and passwords between instances of SQL Server to migrate the gp user from the old SQL Server instance to the new SQL Server instance.

Analysis

The application will be logging in with the gp account and a pre-defined password stored in the application.

Because you have not provided a password for the new user you created on the new instance the login is failing.

Solution

You will have to create the SQL Server login with the above mentioned method provided by Microsoft. Essentially the scripts create two procedures on the source instance in the master database. When you execute EXEC sp_help_revlogin it creates an output containing a CREATE LOGIN ... statement for each SQL Server Login which contains the SID and encrypted password of the original logins.

  The output script that the sp_help_revlogin stored procedure generates is the login script. This login script creates the logins that have the original Security Identifier (SID) and the original password.

You then create the required SQL Server Login on the target SQL Server Instance by running the corresponding CREATE LOGIN.... statement for the gp login on the target instance.


Identify slow query without slow query logs in mysql server

If you do not want to enable the slow query log at all, I have a suggestion
You can use pt-query-digest over an interval of time.
I have suggested this a few times in the DBA StackExchange

Nov 24, 2011 : MySQL general query log performance effects
Apr 24, 2012 : Investigate peak in MySQL throughput
Jul 26, 2012 : What is running right now?

If you look at my Nov 24, 2011 link, I provided a shell script you can crontab to launch pt-query-digest.
GIVE IT A TRY !!!


Issues after schemas migration from mysql 4 to mysql 5.6

There are two things that came into focus once you posted the table structure

InnoDB Table has Fulltext Index
Your Character Set is Hebrew

Would you believe someone asked 7 years ago Does MySql full text search works reasonably with non-Latin languages (Hebrew, Arabic, Japanese…) ???

You should make sure you have that the Hebrew character set in your MySQL installation

mysql> select * from INFORMATION_SCHEMA.CHARACTER_SETS where CHARACTER_SET_NAME='Hebrew';
+--------------------+----------------------+-------------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION       | MAXLEN |
+--------------------+----------------------+-------------------+--------+
| hebrew             | hebrew_general_ci    | ISO 8859-8 Hebrew |      1 |
+--------------------+----------------------+-------------------+--------+
1 row in set (0.00 sec)

along with the necessary collations

mysql> select * from INFORMATION_SCHEMA.COLLATIONS where CHARACTER_SET_NAME='Hebrew';
+-------------------+--------------------+----+------------+-------------+---------+
| COLLATION_NAME    | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN |
+-------------------+--------------------+----+------------+-------------+---------+
| hebrew_general_ci | hebrew             | 16 | Yes        | Yes         |       1 |
| hebrew_bin        | hebrew             | 71 |            | Yes         |       1 |
+-------------------+--------------------+----+------------+-------------+---------+
2 rows in set (0.00 sec)

You could try creating a new table with the same structure and loading it.

METHOD #1

CREATE TABLE cars_ftr_a LIKE cars_ftr_a_new;
INSERT INTO cars_ftr_a_new SELECT * FROM cars_ftr_a;
RENAME TABLE cars_ftr_a TO cars_ftr_a_old,cars_ftr_a_new TO cars_ftr_a;

METHOD #2

ALTER TABLE cars_ftr_a ENGINE=InnoDB;

Then, go look into the error log and see if those same warnings reappear. If they do not, you are good to go. You should also look into tuning the InnoDB Fulltext Options.

UPDATE 2017-08-22 23:17 EDT

METHOD #3

Another thing you can try is to drop the Fulltext Index and create it again

ALTER TABLE cars_ftr_a DROP INDEX `fts_string`;
ALTER TABLE cars_ftr_a ADD FULLTEXT `fts_string` (`fts_string`);

Then, go check the logs to see if the same errors came back or not.

UPDATE 2017-08-23 11:04 EDT

METHOD #4

CREATE TABLE cars_ftr_a LIKE cars_ftr_a_tmp;
ALTER TABLE cars_ftr_a_tmp DROP INDEX `fts_string`;
INSERT INTO cars_ftr_a_tmp SELECT * FROM cars_ftr_a;
RENAME TABLE cars_ftr_a TO cars_ftr_a_xxx,cars_ftr_a_tmp TO cars_ftr_a;
ALTER TABLE cars_ftr_a ADD FULLTEXT `fts_string` (`fts_string`);

Load new table without Fulltext Index. Then create Fulltext Index last.


How to identify and remove the SQL table row which contains UTF 8 BOM characters

Balu, the answer you posted will truncate the value to 30 characters because the no length was specified for the CAST and CONVERT declarations of the varchar and nvarchar data types.

Consider using STUFF instead:

--nvarchar example
UPDATE dbo.YourTable
SET UnicodeColumn = STUFF(UnicodeColumn, 1, 1, '')
WHERE CAST(LEFT(UnicodeColumn, 1) AS binary(2)) IN(0xFFFE, 0xFEFF);

--varchar example
UPDATE dbo.YourTable
SET NonUnicodeColumn = STUFF(NonUnicodeColumn, 1, 2, '')
WHERE CAST(LEFT(NonUnicodeColumn, 2) AS binary(2)) IN(0xFFFE, 0xFEFF);


PostgreSQL WAL file database system identifier wrong

Can it be what archive already filled with WAL from another installation? Postgres documentation specifies that archive_command should not overwrite WAL files in archive (https://www.postgresql.org/docs/9.6/static/continuous-archiving.html):

  The archive command should generally be designed to refuse to overwrite any pre-existing archive file. This is an important safety feature to preserve the integrity of your archive in case of administrator error (such as sending the output of two different servers to the same archive directory).

So if archive is set up correctly it will refuse to override WAL and restore will fail.


SQL Server database Mirroring on different servers

So I want to know can we perform mirroring with different SQL Server instances? Or we can only do it with instances on same server?

Yes of course you can do it. Perhaps it is best idea or a suggested good practice to keep Principal mirror and witness on different servers. Of course Principal and mirror would be different servers.Have a look at article Prerequisites, Restrictions, and Recommendations for Database Mirroring and Things to consider before database setting database mirroring

  Also in mirroring how many instance we can configure as a Mirroring server ?

Question is little incorrect or not properly formed. For one principal you can have only one mirror server. Its is not like Logshipping where you can have multiple standby servers for one primary server.

  And is it a right way to achieve database availability? Or is there any other/advance way to achieve database availability and security?

To answer this question to absolute correctness one would require to know your environment RPO and RTO. A simple SQL server cluster with mirroring can work good. You can even go storage replication, SRDF various technologies for Hardware redundancy. Database mirroring almost provides a high availability for SQL Server database. If you want OS redundancy as well create SQL Server cluster and then move databases to cluster and then create database mirroring for the desired database.

PS: In whole question you did not provided what error you got while configuring mirroring. Nonetheless if you read the articles I gave you would be able to configure it correctly


Error installing EM13 templates in Oracle 12 C

The column ACDRROWTSINTCOL# was added to a few dictionary tables, including TAB$, in version 12.2.

The EM templates are for version 12.1.0.2. The download page clearly states that the template should be used with 12.1.0.2.

http://www.oracle.com/technetwork/oem/enterprise-manager/downloads/db-templates-3233309.html

  Note: You can use following DB templates only with EM 13.2.0.0 release. Refer to the EM 13.2.0.0 Install guide to learn how to use these DB templates to setup database that will house the EM repository 13.2.0.0 DB templates are only for 12.1.0.2 version of DB with config types [ Single Instance and PDB ]


You are looking at the problem wrong.

Assuming full recover; the size of your transaction log (T-log) is determined by your work load and how often you take log backups.

It is completely reasonable to have a 3 TB database with a 1 GB T-log if there is a lot of old data, that has simple reports run against it occasionally.  Backup the T-logs hourly and it may never grow.

In most cases you will want to take T-log backups every one to four hours.  How much space is required to hold the T-logs for your heaviest work load in that time defines how big your t-log should be (plus a bit extra just in case).

If you have a virtual server, use a separate drive for for the T-logs.  For a brand new build, in most cases I start with 10GB of disk and start the ndf file at 250MB, watch it grow (auto grow 50MB) for a few days or weeks and then decide "What is the right amount of VLFs for my requried log size?" then I adjust them.  I recheck about every 3 months.

See my post about VLF files here

Edit

  not matter what downtime

There is no down time for the fix, you want to do it off peak hours partly to reduce resource competition, but mostly because you want to get the ndf file as small as possible before you start a planned growth.  It is easiest to do this when there is little or no activity.

  tune autogrowth to prevent VLF fragmentation

Not really sure what you mean by this.  VLF files are written to in sequence. One gets full, the next available in line gets written to.

  I can imagine that log backups will take longer because the log backup

Maybe a minuscule impact, but nothing worth worrying about.

  impact of a scenario where a transaction log is huge

If you are not currently having performance issues, impact is mostly limited to restore times.  Which is more related to the number of VLFs as opposed to the size of the file.

If you are having performance issues, they are likely tied to indexes or something data or procedure related or possibly hardware.  If it the performance issue is index related, you want to fix it and get regular index maintenance started, then address VLF files (because heavy index maintenance is hard on t-logs, the first time)

In short, do VLF maintenance when you have the opportunity. It has been ugly for a long time.  You are not going to see any magical improvements in daily performance.


When upgrading to mongodb 3.0, should I upgrade authentication schema after upgrading the storage engine to WiredTiger?

As part of the MongoDB 3.0 upgrade it is strongly recommended that you upgrade to the SCRAM-SHA-1 authentication scheme. New users will have their credentials created in this format by default; mixed authentication schemas will likely lead to confusion.

You will need to upgrade your client driver & admin tools (if you're using a GUI) in order to use the WiredTiger storage engine. Drivers/tools that have been updated for MongoDB 3.0 will also support the new authentication method.

As per the note on the SCRAM-SHA-1 documentation (referring to a future major release which would like be 3.2.x):

  The next major version of MongoDB is likely to remove all support for MONGODB-CR.


Is it possible to bulk insert using CTE in MySQL?

The task is not defined completely, but nevertheless:

-- Parameters
SET @folderId:=2;
SET @user_ids:='11,14';
SET @access_mask:=2;

-- Insert/update access masks for single users
INSERT INTO FOLDER_ACCESS (FOLDER_ID, USER_ID, ACCESS)
WITH RECURSIVE
folder_ids AS ( SELECT f.id
                FROM FOLDER AS f
                WHERE id = @folderId
              UNION ALL
                SELECT f.id
                FROM folder_ids
                JOIN FOLDER AS f ON f.parent_id = folder_ids.id ),
user_ids AS ( SELECT SUBSTRING_INDEX(@user_ids, ',', 1) user_id,
                     CONCAT(SUBSTRING(@user_ids FROM 1 + LOCATE(',', @user_ids)), ',') slack
            UNION ALL
              SELECT SUBSTRING_INDEX(slack, ',', 1),
                     SUBSTRING(slack FROM 2 + LENGTH(SUBSTRING_INDEX(slack, ',', 1)))
              FROM user_ids
              WHERE slack )
SELECT folder_ids.id, user_ids.user_id, @access_mask
FROM folder_ids, user_ids
ON DUPLICATE KEY
UPDATE ACCESS = @access_mask;

fiddle

The query for groups access masks update is absolutely similar.

  If a row already exists for the given combination of folder id, user id/user group id, then update the access value for that row

"Update" is too vague term.

I think that the flag of access mask change type must exist. For example, @type, with the values:

unconditional replace
set bits
clear bits

And update clause will be

UPDATE ACCESS = CASE @type WHEN 1 THEN @access_mask
                           WHEN 2 THEN ACCESS | @access_mask
                           WHEN 3 THEN ACCESS & ~@access_mask
                           ELSE ACCESS
                           END


Are there any free online collaboration database schema tools?

You can use Vertabelo. Vertabelo is an online database designer working under Chrome and Safari developed by company I work for.

Vertabelo focuses on online collaboration in the visual database schema creation area. The most important feature of this tool is that it allow to share database models across the team and collaborate on them via web browser. It also allow to have public model link with possibility of embeding diagrams in your webpage/blog/wiki.

Others Vertabelo features include:

Support for PosgreSql, MySQL, Oracle, MS SQL Server, DB2, SQLite, HSQLDB
Dynamic/Visual search
Live database model validation
Model versioning
Forward engineering
Reverse engineering

Vertabelo is free to use for academic projects and have commercial versions for larger database projects.


Try defining the length of the varbinary in your convert statement as varbinary(max). Per https://technet.microsoft.com/en-us/library/ms188362(v=sql.110).aspx it would default to 30.

You can replicate that with the following script:

DECLARE @STRING VARCHAR(100);
SELECT  @STRING = REPLICATE('X', 100);

SELECT  LEN(@STRING) AS String_Length,
        LEN(CONVERT(VARBINARY, @STRING)) AS Varbinary_Length ,
        LEN(CONVERT(VARBINARY(MAX), @STRING)) AS VarbinaryMax_Length;


CREATE INDEX CONCURRENTLY cannot run inside a transaction, and all functions are transactions, (but ordinary create index can).

Perhaps something like PG_AGENT can be leveraged to create the index upon command from your trigger.

https://www.pgadmin.org/docs/pgadmin4/3.x/pgagent.html

Alternatively you could do a regular create index when you create the table, an empty table will not be locked for long during a create index.


Getting Error ORA-00902 - Invalid Identifier when creating a foreign key

Your syntax is off. You need to actually define the column that has the constraint on it, and add the constraint at the end.

CREATE TABLE Vehicle
(
  Registration VARCHAR2(7) PRIMARY KEY,
  Vehicle_type_id INT,                      -- add this
  GVW INT,
  Year INT,
  Body VARCHAR2(15),
  CONSTRAINT vehicle_type_fk                -- constraint name
    FOREIGN KEY (Vehicle_type_id)           -- constrained column
    REFERENCES Vehicle_type(Vehicle_type_id)
);

Also you'll need to drop the vehicle table before the vehicle_type table, otherwise you'll get a dependency error:

ORA-02449: unique/primary keys in table referenced by foreign keys


Backups of the transaction log will not help your CPU bottleneck.

How many deadlocks per hour or day do you have?
What is you CXPACKET wait value?
How much blocking do you have?

I am not saying to stop your transaction log backups, I am saying that this won't help the CPU bottleneck.


Efficiently storing sets of key-value pairs with wildly different keys

So what I am looking for is a way to store a large number of
  activities that have almost no fields in common in a way that makes
  reporting easier.

Not enough rep to comment first, so here we go!

If the primary purpose is reporting and you have a DW (even if it isn't star schema) I'd recommend attempting to get this into a star schema. The benefits are fast, simple queries. The downside is ETL, but you're already considering moving the data to a new design and ETL to star schema is likely simpler to build and maintain than an XML wrapper solution (and SSIS is included in your SQL Server licensing). Plus it starts the process of a recognized reporting/analytics design.

So how to do that... It sounds like you have what is known as a Factless Fact. This is an intersection of attributes that define an event with no associated measure (such as a sales price).
You have dates available for some or all of your activities? Likely you should really have an intersection of an Activity, Site, and Date(s).

DimActivity - I'm guessing there is a pattern, something that can allow you to break these down into at least relatively shared columns. If so, you may have three? five? dimensions for classes of activities. At worst you have a couple consistent columns, such as activity name, you can filter on, and you leave general headings such as "Attribute1" etc. for the remaining random details.

You don't need everything in the dimension - there (likely) shouldn't be any dates in the Activity dimension - they should all be in the fact, as Surrogate Key references to the Date dimension. As an example, a Date that would stay in a person dimension would be a date of birth because it's an attribute of a person. A hospital visit date would reside in a fact, as it is a point in time event associated with a person, among other things, but it is not an attribute of the person visiting the hospital. More date discussion in the fact.

DimSite - seems straight forward, so we'll describe Surrogate Keys here. Essentially this is just an incrementing, unique ID. Integer Identity column is common. This allows separation of DW and source systems and ensures optimal joins in the data warehouse. Your Natural Key or Business Key is usually kept, but for maintenance/design not analysis and joins.
Example schema:

CREATE TABLE [DIM].[Site]
(
 SiteSK INT NOT NULL IDENTITY PRIMARY KEY
,SiteNK INT NOT NULL --source system key
,SiteName VARCHAR(500) NOT NULL
)

DimDate - date attributes. Make a "smart key" instead of an Identity. This means you can type a meaningful integer that relates to a date for queries such as WHERE DateSK = 20150708. There are lots of free scripts to load DimDate and most have this smart key included. (one option)

DimEmployee - your XML included this, if it is more general change to DimPerson, and fill with relevant person attributes as they are available and pertinent to reporting.

And your fact is:

FactActivitySite
DimSiteSK - FK to DimSite
DimActivitySK - FK to DimActivity
DimEmployee - FK to DimEmployee
DimDateSK - FK to DimDate

You can Rename these in the Fact, and you can have multiple date keys per event. Facts are typically very large so avoiding updates is typically good... if you have multiple date updates to a single event you may want to try a Delete/Insert design by adding a SK to the fact which allows selection of "update" rows to be deleted then inserting latest data.

Expand your Fact dates to whatever you need:
StartDateSK, EndDateSK, ScheduledStartDateSK.

All dimensions should have an Unknown row typically with a hardcoded -1 SK. When you load the fact, and an activity doesn't have any of the included Dates it should simply load a -1.

The fact is a collection of integer references to your attributes stored in the dimensions, join them together and you get all your details, in a very clean join pattern, and the fact, due to it's data types, is exceptionally small and fast. Since you are in SQL Server, add a columnstore index to increase performance further. You can just drop it and rebuild during ETL. Once you get to SQL 2014+ you can write to columnstore indexes.

If you go this route research Dimensional Modelling. I'd recommend Kimball methodology. There are lots of free guides out there too, but if this will be anything other than a one off solution, the investment is likely worth it.


The DMV's themselves are the same between same builds of SQLServer and give you a snapshot or a cumulative counter of the status of your server at any given time.

You cannot restore the database to a new server with the metadata from the old one. You can make snapshots of some of the performance metrics into a table but you cant restore the state of the DMV's. You can however grab the query plans from your production server and use plan guides to have the same behavior in test, if you want to mimic specific behavior of a single query that is not behaving in production environment but runs ok in test.

You can then of course use the Management Data Warehouse to collect information about performance of queries and the system to find which queries to optimize or third party tools that can collect query plans and wait statistics on the server. Here is a way to collect the query plans using sp_whoisactive


SQL Server : Tracking a slow running query on completion

A quick-and-dirty way to get this information is to run something like this query while the query you wish to observe is still running.

select
    st.text,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    qs.creation_time,
    qs.*
from sys.dm_exec_query_stats qs
join sys.dm_exec_requests r
    on r.sql_handle = qs.sql_handle
cross apply sys.dm_exec_sql_text(qs.sql_handle) st

Note: this will only capture queries that are running at that moment. Additionally, the query stats DMV does not persist across reboots and may also be flushed when under pressure. Joining on sql_handle gets you query stats for that exact statement. You can find query stats for "similar" queries (similar logic with difference occurring only in literal values) by joining on query_hash.


Finding the query causing the missing index DMVs

Short answer: you can't with 100% accuracy.

Long answer: you can query the plan cache to identify plans with missing index warnings and compare the results with what you find in sys.dm_db_missing_index_* DMVs. Here's a script that you could use to query the plan cache. If the plan doesn't get cached or gets pushed out the cache for any reason, you won't find any matches, so we're back to the short answer.

To be 100% sure, you would have to trace the workload and capture the plans with missing indexes. It's not lightweight and it's not trivial. Here's a script to do that. With a small effort it could be ported to Extended Events.


Running a TPC-C Benchmark Without sleep() i.e. key-in + think time

Apologies for the late response to this, but there is a key reason as to why you are not observing an expected smooth behavior:  You have a client-server model violation when you eliminate think time.

The client server model is built upon a foundation that not all user are using system resources at the same time.  This is different than the mainframe model with terminals where all users are being serviced at the same time by the core host.   With client-server distributed processing exists and there is a delay between requests during which the resources are free'd to service the needs of other inbound users.  Normally when you place a host under load with think time these free internals tend to become smaller and smaller and as load increases you eventually reach a tipping point of no free resources to service requests.

By eliminating the think time you eliminate the free resource window to service additional requests and your tests become invalid as a predictor in real world performance.   If you ever hear a performance tester tell you that they have eliminated think time and every one user is equal to n real world users, fire them on the spot.


Audit Trail in NoSQL vs. Relational DB, audit trail feature in MongoDB

MongoDB Enterprise and MongoDB Atlas both include auditing capability with options to to filter for audit events including schema (DDL) changes, CRUD operations, authentication & authorization, and replication/sharding commands.

Auditing was first included in MongoDB Enterprise 2.6 (March, 2014).


Case statements for multiple fields when only certain cases exist for each field

The single-UPDATE you have seems reasonable.  However, depending on statistics and how well the Optimizer handles IN, this 2-UPDATE approach may be faster.

Either will benefit from

INDEX(bill_id, stockid)

The order of the columns in the index matters.  bill_id needs to be first for the single-update with the CASE statement.  Doing multiple UPDATEs, as follows, would not care which order the INDEX is written.

UPDATE `billlineitem`
    SET    `rate` =  '40',
           `updated_billitemquantity_at` = '2019-09-06 05:16:06.219'
    WHERE bill_id = 1
      AND stockid = 11;
UPDATE `billlineitem`
    SET    `rate` =  '5',
           `updated_billitemquantity_at` = '2019-09-06 05:16:06.219'
    WHERE bill_id = 1
      AND stockid = 10;

(For updating 15 rows, there would need to be 15 UPDATE statements; this may be impractical.)


You can create a new tablespace on a different disk and then move those tables to that new tablespace.

First create a new table space on a disk that has enough space:

create tablespace large_disk
   location '/path/to/directory/with/more/space'
   owner your_postgres_user;

By making your_postgres_user the owner of that tablespace, the user automatically gets all privileges on that tablespace.

Then you need to find out to which tables those files belong to:

select 16400::regclass, 16405::regclass;

Then move the tables:

alter table public.table_one
    set tablespace large_disk;

alter table public.table_two
    set tablespace large_disk;


Does Oracle 11gR2 is supported on RHEL 8 or Oracle Linux 8?

Most likely you can run it without any issues but it is not supported by Oracle. As Phil suggests in his comment this combination will never be supported as you are mixing an almost out of support database (extended is over on December 2020) with a just added Linux release.

Best solution is use Oracle 12.2 which, in my experience, is a minimal problem migration target for most applications using Oracle 11.  The 12.2 release allows an easy upgrade to Oracle 18 or 19 when the time comes.


Blocking continues after enabling snapshot isolation

If your intention is to avoid readers from blocking writers and visa-versa in the default READ_COMMITTED isolation level, turn on the READ_COMMITTED_SNAPSHOT database option.  This will cause row versioning instead of locking to be used to implement statement-level read consistency.

Although often confused, the ALLOW_SNAPSHOT_ISOLATION option is not related to READ_COMMITTED_SNAPSHOT.  ALLOW_SNAPSHOT_ISOLATION allows the separate SNAPSHOT isolation level to be used to provide multi-statement read consistency but code changes are required to use it.  The READ_COMMITTED_SNAPSHOT option provides statement-level read-consistency without code changes, although one should be aware of the implications the locking behavior have on apps that rely upon locking.


User can't right-click SQL Azure database in SSMS

Please run the following statement on the Master database and that should do the trick

CREATE USER LoginName FROM LOGIN LoginName

Hope this helps.

Regards,

Alberto Morillo


Is there a Storage-Engine/DB that logs all changes and allows selective rollback of specific changes?

Here's the problem that I'm struggling with, and that I think you will find when attempting to find this type of system. (Particularly when it goes to requirements 7 & 8.)

Let's say that you have User A who changes a value from 1 to 2.  Then User B changes it from 2 to 26.  If you attempt to rollback User A's value, what do you roll it back to?  Do you change it back to 1 or do you leave it at 26?  Do you do some strange business logic and make it 25?

There are several possible scenarios, but ultimately, there is no generic way of handling this type of data.  If you don't roll back, then you fail to satisfy Rule 8.  If you do roll back, then you fail to satisfy Rule 7 (allowing ONLY changes from a certain user to roll back).  If you do some crazy math rules, then each rule will have to be very specific to the column that modify.

I don't think that there are any databases that can handle this on a general basis.  It is possible to roll back on a point-in-time basis, but that completely breaks Rule 7 (allowing you to roll back changes from a certain group).

Outside of a custom-written, specific use application, I don't see how it's possible to implement 7 & 8 on a generic database system.


Using a table swap for a data load but I have triggers

This is a pretty crappy workaround, since there really should be an easy way to rename a table or transfer it to a different schema and keep its triggers (I've put in a bat-signal to an MS contact to see about this). But I think it's better than carrying around triggers on both tables that have the live name hard-coded.

Drop the trigger on the stage version of the table. This isn't the one people are touching. Then alter your process slightly:

Step 3.5:

DECLARE @sql NVARCHAR(MAX);
SELECT @sql = OBJECT_DEFINITION(OBJECT_ID('dbo.table1_trigger_name'));
DROP TRIGGER dbo.table1_trigger_name;

Step 6.5:

EXEC sp_executesql @sql;

This will re-create the trigger on the right version of the table. And actually, contrary to my comment, does not require the trigger code to be stable over time, since every time you run it, it will grab the current definition.

Now, you will not be able to have any GO / batch separation between those steps, and you should regularly keep a copy of the trigger in source control just in case anything every goes wrong.


Cascading PostgreSQL triggers between three tables

To resolve your problem, I did the following (all of the code below can be found on the fiddle here):
I changed the formatting somewhat - it helps me think - I try to base my SQL coding standards on this, but that's, as Fr. Jack would say, an ecumenical matter (if you're not a Fr. Ted fan, don't worry...).
The crux of your problem is that you have a cascading chain of FOREIGN KEYs from job to phase to phase_step - which is good - helps keep track of what work goes with what contract &c.
The problem is with your code here:
create or replace function test.setup_phases () returns trigger as $$
    begin
        insert into test.phase_steps (job_id_fk,phase_type_lu)
            values
                (new.jobs_id,'RESEARCH: JOB_INPUT'),

You have setup_phases, BUT underneath that, you have insert into test.phase_steps (job_id_fk,phase_type_lu) - you "skip" the INSERT INTO phases step - so your chain of FOREIGN KEYs is broken.
Now, I imagine that your work flow is something like this:
You receive a job - the first phase of which is normally RESEARCH, so I created the phase table as follows (job remains unchanged):
CREATE TABLE phase
(
  phase_id      SERIAL PRIMARY KEY,
  job_id_fk     INTEGER references job (job_id),
  phase_type_lu TEXT DEFAULT 'RESEARCH',

  CONSTRAINT phase_type_ck CHECK (phase_type_lu IN ('RESEARCH', 'SURVEY'))
);

So, then I did this:
CREATE OR REPLACE FUNCTION setup_phase_bis()
RETURNS TRIGGER AS
$$
BEGIN
  INSERT INTO phase (job_id_fk) VALUES
    (NEW.job_id);
    RETURN NEW;
END;
$$ LANGUAGE PLpgSQL;

and:
CREATE TRIGGER setup_phase AFTER INSERT ON job
FOR EACH ROW EXECUTE PROCEDURE setup_phase_bis();

So, now, when I create a job, it sets up a RESEARCH phase. You missed this in your code. What happens now when I INSERT a job is the following:
INSERT INTO job (job_type_lu) VALUES ('NT-TYP');

and:
SELECT * FROM job;
SELECT * FROM phase;
SELECT * FROM phase_step;

Result:
job_id  job_type_lu
1   NT-TYP
phase_id    job_id_fk   phase_type_lu
1   1   RESEARCH
phase_step_id   phase_id_fk step_type_lu

Note: no phase steps because I haven't done that yet.
So then I copied your code - with the notable exception (pardon the pun...) of the lines (see fiddle):
  IF NEW.job_id_fk IS NULL THEN
    RAISE EXCEPTION 'You must supply a job_id for this phase...';
  END IF;

Not having this provokes the error message you are receiving...
So, now, when a job is inserted, the job cascades to (RESEARCH) phase and the phase cascades to the appropriate steps - as follows:
INSERT INTO job (job_type_lu) VALUES ('XXXXX');

And:
SELECT * FROM job;
SELECT * FROM phase;
SELECT * FROM phase_step;

Result:
job_id  job_type_lu
1   NT-TYP
2   XXXXX
phase_id    job_id_fk   phase_type_lu
1   1   RESEARCH
2   2   RESEARCH
phase_step_id   phase_id_fk step_type_lu
1   2   RESEARCH: JOB_INPUT
2   2   RESEARCH: WATER
3   2   RESEARCH: OTHER
4   2   RESEARCH: REVISION_ATEAM
5   2   RESEARCH: REVISION_CLIENT

So, we have our cascade.
Now (again, making assumptions about your workflow) - if the client is happy with the research phase, they'll want to progress to the SURVEY phase and so you will wish to enter that into the system but not as a new job (hope I'm OK so far... :-) )
So, now we do this:
INSERT INTO phase (phase_type_lu) VALUES ('SURVEY');

BUT, there's no associated job_id, so, thanks to the EXCEPTION, this fails as follows:
ERROR:  You must supply a job_id for this phase...
CONTEXT:  PL/pgSQL function setup_phase_steps() line 5 at RAISE

But, if we do this:
INSERT INTO phase (job_id_fk, phase_type_lu) VALUES (2, 'SURVEY');

we get:
SELECT * FROM job;
SELECT * FROM phase;
SELECT * FROM phase_step;

Result:
job_id  job_type_lu
1   NT-TYP
2   XXXXX
phase_id    job_id_fk   phase_type_lu
1   1   RESEARCH
2   2   RESEARCH
4   2   SURVEY
phase_step_id   phase_id_fk step_type_lu
1   2   RESEARCH: JOB_INPUT
2   2   RESEARCH: WATER
3   2   RESEARCH: OTHER
4   2   RESEARCH: REVISION_ATEAM
5   2   RESEARCH: REVISION_CLIENT
6   4   SURVEY: SCHEDULING
7   4   SURVEY: FIELDING
8   4   SURVEY: REVISION_ATEAM
9   4   SURVEY: REVISION_CLIENT

Our SURVEY phase steps have been propagated with the correct FOREIGN KEYs. So, all is rosy in the garden? I ran a final test at the bottom of the fiddle (not shown for brevity), and all appears OK!
For future reference, it would be great if you could provide appropriate fiddles with your questions and also if you could give us the version of PostgreSQL that you are running - help us to help you!
p.s. welcome to the forum!


How to export data in Oracle database into CSV file with UTF-8 format?

UTL_FILE.PUT_LINE_NCHAR Procedure

  This procedure writes the text string stored in the buffer parameter
  to the open file identified by the file handle. With this function,
  you can write a text file in Unicode instead of in the database
  character set.

So you could try something like this:

-- Write the row to the file
UTL_FILE.PUT_LINE_NCHAR(file, TO_NCHAR(row_value));


As documented in the manual there are two ways to specify an array constant
On is to use the array[...] notation:
SELECT *
FROM people
WHERE codes && array[5294,1933]::bigint[]

Alternatively use a string that uses {..}
SELECT *
FROM people
WHERE codes && '{5294,1933}'::bigint[]

I prefer the first one, as that also doesn't have problems when the data type itself requires quoting e.g. for text arrays.


You state:

  MAX Memory set at 215GB

As @Shanky stated in the comments, set max server memory (MB) to 128 GB via sp_configure as that's the limit of usable memory for SQL Server Standard Edition.

I've run into a similar issue with SQL Server Express edition when I had it set higher than it's usable limit.


db_datareader role along with selected table update

grant update on object::PROD.CustomerRelations to db_user

https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-object-permissions-transact-sql?view=sql-server-2017


'Privileges dependent' error when try to drop a role/user even if there are no database attached and no role attributes

PostgreSQL isn't MySQL. You can't drop databases or users without being attached to some database.  Based on the error message, the database you are attached to in order to execute the DROP USER statement is the one whose public schema has privileges granted to the doomed user.

Schema grants are not attributes of a role, so don't show up in \du.  To see them, you would use \dn+.


Improving the performance of procedures showing warnings for implicit conversions

sp_BlitzCache will break down implicit conversion information for you, for seek affecting converts.

For example, with this query (for brevity):

DECLARE @butthead SQL_VARIANT = N'your mom'
SELECT COUNT(*) FROM dbo.Comments AS c WHERE c.Text = @butthead

And this plan, it'll return results like this:

If you click on the implicit conversion info column, it'll give you a break down of what happened where:

So while it doesn't search your plan cache specifically for that issue, it will give you your most resource intensive queries and warn you when implicit conversion is a problem, and then break down the details.

Hope this helps!


SQLVDI and Backup Database event viewer errors

There's an external backup running via VSS (Volumne Shadow Copy Service), might be a VM snapshot, SAN snapshot or similar. It freezes I/O briefly to quiesce it since the files are open.

Reach out to your Windows admin for what's going on during that timeframe.

See this related post.


mariadb / mysql wont change table_cache / table_open_cache

The operating system has a low limit.  In Unix, see ulimit -n, and change it.  Some versions have an unnecessarily low limit of 1024.

Most, but not all, things set by my.cnf (my.ini) stay set.  table_open_cache is one that is changed based on the environment.  Use SHOW VARIABLES to see the real values.  Or SHOW VARIABLES LIKE 'table_open_cache' to see the one setting.


Does it makes sense to index a primary key if it is not used?

In MySQL, if you have an auto_increment column, it MUST also be a key/index (either primary or a unique or a non-unique one, so any index really) where auto increment column is the first in the index/key definition.

For any InnoDB tables that do not have a PRIMARY or UNIQUE NOT NULL key defined, MySQL will implicitly create (a 6-byte) internal key under the hood and use it as the clustered index.

This clustered key (the PRIMARY or the first UNIQUE NOT NULL or the 6-byte internal) is always included in ALL secondary indexes for InnoDB tables in MySQL.

In your situation, dropping the PRIMARY key will only result in MySQL creating an implicit one anyway (unless you define another PRIMARY or UNIQUE NOT NULL key explicitly). This also applies only to InnoDB tables.


SQL Server 2008 R2 SP1 - Can't install "Full-text Search"

For all people which have the same problem, I solved it this way:

I installed a completely new "SQL Server 2012 SP2" instance (with fulltext-search), transferred my databases to the new instance and uninstalled the old one.


SSMS 2014 working with SQL Server 2005 instances

My motto is to always use the most recent version of the tools and always have tools >= highest engine you manage. Generally, most of everything in modern versions is backward compatible going back as early as 2005 instances.

There are a couple of exceptions, in addition to those others have mentioned:

For SQL Server 2000, I always install the 2000 client tools.
If you use DTS/SSIS you will want to have the tools relevant to the version of your packages. If you open a 2008 package in 2014 and save it, you will cause problems for other users still using older versions. This may also be true for maintenance plans.

The nice thing is that you can install both the 2005 version of the tools as well as the most recent version side-by-side. This way, if you come across any compatibility issues, you can fire up the older version.

In general, you should be able to use the newer version for just about everything, and not worry about any negative effects on your data (full disclosure though - I don't use the UI functionality for a whole lot, particularly the buggy designers).


OleDBConnection.Close() very slow on server

In my experience,

One of the access database exceeded 20000 records and the size was around 150 MB. It started to slow down and performance plummeted. So moved to SQL.

You may consider using SQL compact edition, or sql lite


Duplicate key violates unique constraint

Your rule does not have a WHERE clause and any update is trying to modify the whole users table.

You can/should add WHERE id = old.id:

CREATE OR REPLACE RULE students_data_update AS
    ON UPDATE TO students_data DO INSTEAD
UPDATE users
SET f_name = new.f_name,
    l_name = new.l_name,
    --- ...
WHERE id = old.id ;               --- added

Note: I had never used the RULE system before in Postgres. But the above suggestion worked when tested at SQL-Fiddle.


Snowflake/S3 Pipeline: ETL architecture Questions

see: https://docs.snowflake.com/en/sql-reference/sql/copy-into-location.html

Depends... you can use SNOWFLAKE TASKS to copy data from table to external location (s3/azure..). You can also use any other system which can run commands in snowflake

Same as 1. I think you need use (js)UDF to make your dynamic SQL (filename part) and run it if you use snowflake only solution. Ie. Js UDF, which executes your SQL

see: https://stackoverflow.com/questions/62403809/how-to-write-dynamic-queries-in-snowflake-user-defined-functions

You could use snowpipe and listen events, or run command from ML or just have task that tries to stage new files from ML output s3 folder, which to my knowledge does not cost compute to try and fail to find new files

I don't know about that, maybe report after you have imported it into snowflake

reporting from task history could be one way, not my expertise

So in practise 1 ,2 ,3 can be done using snowflake task only or tasks and snowpipe using files into s3 as trigger. Data can be also enriched using external function calls in snowflake query which uses udf to make some call to some api.
Assuming that your team is small and you do not yet have company way of doing things, i would recommend to start using tasks + snowpipe to handle simple data staging. I personally use Matillion because i have one, but if you do not have one, it's cheaper just to start learn snowflake way and do things there. Learning curve is not that bad, it has it own problems, but those are quite easy to handle. Most of them were communication to ML data producers to keep output schema stable
Only thing which changes if you have company way of doing thing is that those programs orchestrate copy into s3 command and copy from s3 commands in snowflake and run ML in sagemaker ( i do not know about that one to say anything useful)


Is there a standard database layout for repetitive information?

You need two related tables. E.g.:

Table "Test": TestId PK, operator, test_date

Table "Result": ResultId PK, TestId FK, Object, TestResult

Where the "Test" table is the main table and the "Result" table is the details table. Test.TestId is a primary key (PK). Result.TestId is a foreign key (FK) related to Test.TestId. I.e. you have a one-to-n relation between "Test" and "Result".

In Access' relationships editor it would look like this:

Splitting a table into different tables to avoid repeating information is called Database normalization (Wikipedia)


How to import values of a JSON to PostgreSQL?

You need to unnest the first level, and the second level in two steps:

Use jsonb_each() instead:

select d.val ->> 'value1' as value1,
       d.val ->> 'value2' as value2
from gugu g
  cross join jsonb_each(g.data) as d(ky,val);

Online example


Add 0 into array_agg if there is no matching data

For the first point, you can try this:

COALESCE(array_agg(DISTINCT pr.position), '{0}'::integer[])

The same can be achieved by a CASE, too:

CASE WHEN array_agg(id) IS NULL
     THEN '{0}'::integer[]
     ELSE array_agg(id) END

The second problem can be handled by a HAVING cause:

...
GROUP BY ...
HAVING array_length(COALESCE(array_agg(DISTINCT pr.position), '{0}'::integer[]), 1) > 1;


Load data into postgresql from file with trailing delimiter

You could also pre-process the file as suggested by Vérace:

  Or  sed s'/.$//'

from How to shave off last character using sed? by codaddict on Stack Overflow.

Or sed -i ... (inplace - I tend to avoid this, because in case of a mistake, you've lost your original file) or sed .... > table.txt.


One option would be to use XP_CMDSHELL and issue a simple DIR command and load the information into a table variable.  Then, delete the rows that don't have the word free and you should get the line that indicates the amount of free space.

DECLARE @DirectoryInfo TABLE (TextLine VARCHAR(max))

INSERT INTO @DirectoryInfo
EXEC xp_cmdshell 'dir <YourRemoteDirectory>'

DELETE
FROM @DirectoryInfo
WHERE isnull(TextLine, '') NOT LIKE '%free%'

SELECT *
FROM @DirectoryInfo

16 Dir(s)  6,247,769,628,672 bytes free


Unexpected Table Scan with Parameterized LIKE

The claim that it occurs only for non-clustered indexes is due to the fact that you only had two columns - one being the indexed column, the other being

the clustering key (i.e. the other column) if there were a clustered index
the RID if there were no clustered index

In the second case, to satisfy the SELECT * (all columns) portion of your query, it would have to perform an expensive lookup, so the generic (robust) plan to perform a 10,000* record table scan is chosen instead.  In the first case, the index is all that is required to satisfy the SELECT clause.

*It should be noted that the number of records and index cardinality play a role in determining the plan as well.

With more columns, the plan predictably switches to an CLUSTERED INDEX SCAN for the parameterized LIKE statement even with a clustered index, per the revised test below.

IF (OBJECT_ID('tempdb.dbo.#tblTest') IS NOT NULL)
    DROP TABLE dbo.#tblTest
GO

CREATE TABLE dbo.#tblTest (
    ID INT IDENTITY(1, 1),
    SerialNumber VARCHAR(50),
    Othercolumn1 uniqueidentifier default (newid()),
    RowVer timestamp
)
GO

-- Populate the table with 10,000 rows
SET NOCOUNT ON
DECLARE @i INT
SET @i = 0

WHILE @i < 10000
BEGIN
    INSERT INTO dbo.#tblTest (serialnumber) VALUES(CAST(@i AS VARCHAR(10)))
    SET @i = @i + 1
END
GO

-- To recreate the issue, the table must be a heap.
ALTER TABLE dbo.#tblTest ADD CONSTRAINT PK_tblTest PRIMARY KEY CLUSTERED (ID)
GO

-- Create a (non-covering) index on serial number.
CREATE NONCLUSTERED INDEX IX_tblTest_SerialNumber ON dbo.#tblTest (SerialNumber)
GO

DECLARE @Criteria VARCHAR(50)
SET @Criteria = '1234%'

-- This produces a Table Scan.
SELECT *
FROM dbo.#tblTest
WHERE SerialNumber LIKE @Criteria

-- This produces an Index Seek
SELECT *
FROM dbo.#tblTest
WHERE SerialNumber LIKE '1234%'

Here are the plans generated based on my revised table structure. For the schema in the question, the top part becomes a Table Scan and the bottom part becomes an RID Lookup instead of Key Lookup - all else being equal.

One of the more expensive operations in executing a query is first constructing the execution plan. To help this along, SQL Server has a Plan Cache that stores the text of statements and the associated SET settings. The same text using different SET settings can result in different behaviour, so that is planned anew and is stored as a separate entry.

The non-parameterized query is simple to plan - it contains the exact text '1234%'. The VARCHAR index on SerialNumber is easily searched for the portion containing the prefix "1234".  SQL Server also estimates the cardinality of the query and invariably for your data will choose the INDEX SEEK plan.  Any further presentation of the exact query statement (text) to SQL Server will contain the static value '1234%' and is safe to re-execute the same plan efficiently.

On the other hand the parameterized query is stored into the Plan Cache (dictionary) keyed by the statement text ... WHERE SerialNumber LIKE @Criteria.  Even though @Criteria in the current batch contains the value '1234%' and can use an INDEX SEEK, it is quite possible for another user to submit the exact same query with @Criteria set to '%9' instead which would not perform as well using the INDEX SEEK + RID Lookup. This would SELECT 10% of the data which is often over the tipping point at which index seeking is no longer favourable.  For robustness and reusability, the plan cached for this query (and then executed) is the Table Scan version that will cater for the widest range of @Criteria values at average efficiency across the possible values.


What is the definition of a bad index? How do we decide? What logic we should have?

However is the issue this simple?

Absolutely not. Unfortunately it can vary a lot between applications so while there are many "rules of thumb" like the one you state none of them are universally applicable apart from "an index that is never used is a waste".

  Can we call all write > read = bad index?

If that index is for enforcing integrity (i.e. it is a unique index or is supporting a foreign key) then that fact would generally trump a performance requirement. Also, the writes could be mostly at times where it doesn't matter (a bulk update in the middle of the night perhaps) or it could be really important that those reads are as fast as possible so slow writes are a price worth paying.

When thinking about these matters always consider the time period that any figures you are looking at cover. It could be that an index looks under-used this week, but next week is the start of a new reporting period so the users are performing a different set of actions and the index suddenly looks far more useful.

Never use rules-of-thumb as hard-and-fast rules to live by. Use them as suggestions and consider the results carefully with your knowledge of your specific application(s)'s needs.

  can you expand your answer and add more details?

Not without writing a whole book unless you have more specific questions, this is a very wide area. Many whole book have been written on the subject in fact... http://use-the-index-luke.com/ is a good place to start.


Compatibility level when upgrading a SQL Server 2005 database to SQL Server 2012

You can not set the compatibility level of a SQL Server 2005 instance to anything higher.
Hence the upgrade path:

Create SQL Server 2012 Server / Instance.

Option 1: Backup / Restore; change compatibility level.
Option 2: Detach, copy, Attach; change compatibility level.

Option 1 is most used because you have overlap (old is available, new can be tested).
Rollback to previous is certain.

Misread your question. You want to remain at SQL 2005 compatibility: Don't change compatibility level during option 1 or 2. Compatibility is configured at the database level.


Matching multiple rows in a MySQL database

This is usually done using conditional aggregation plus some applied logic:

select order_id,
   case max(case when product_type = 'A' then 1 else 0 end) +
        max(case when product_type = 'B' then 2 else 0 end)
      when 1 then 'A not B'
      when 2 then 'B not A'
      when 3 then 'A and B'
   end
from mytable
where product_type in ('A','B')
group by order_id


When placing an order in a shop, should a shapshop be taken of the products in an order also?

The best way of freezing the information for this would be to have a separate table for invoice details. This would store not  the basic invoice information (date_of_purchase, customer_ID) , but it would store the individual items in the invoice (Invoice_ID, Product_ID, Product_QTY, Product_Price_Per_Unit). Check out this link here, it covers the general idea that I'm talking about.
https://itextpdf.com/en/resources/books/zugferd-future-invoicing/3-simple-invoice-database


Why are there empty transactions in the MySQL binlog?

After some research and further looking into MySQL documentation, it turned out to be it was an error on my part.

I was using mysqlbinlog -s option to view binlog. Because the replication statement is coming over as Row Based Replication, it is not possible to see the actual statement.

  You cannot see on the slave what statements were received from the master and executed.

Advantages and Disadvantages of Statement-Based and Row-Based Replication

So when trying to view the binlog using the mysqlbinlog -s util, it doesn't decode this blob of information and hence shows up as blank/empty commit.

Hope this helps someone else runs into to similar situation.


Answer originally left in comments by David Mann:

If you are unable to run FREETEMPORARY then I believe your only option is to disconnect/reconnect the session. This will cause a cleanup of any temp blob space used by that session. Had a similar issue here where we couldn't touch the app code, so we ended up tweaking their connection pool to expire and reconnect more frequently.

Oracle Metalink Doc ID 802897.1 "How to Release the Temp LOB Space and Avoid Hitting ORA-1652" covers this, for 11g your option is to invoke FREETEMPORARY or to exit session. They also mention a new event 60025 which is supposed to free temp segments when all the temp LOBs for a session are no longer used, but I didn't see any other detail on that.


If I make full/differential/log backups to disk under full recovery
  model it is important to specify MEDIANAME?

NO, it is not important

  Is it true that MEDIANAME is only for tape backups? If not, when and
  why should I specify MEDIANAME in the backup statement?

No it is not just for tape backups,  you can take local disk backup and specify media name. More over its a name given to particular backup set.For example

BACKUP DATABASE AdventureWorks2012
TO DISK='X:\SQLServerBackups\AdventureWorks1.bak',
DISK='Y:\SQLServerBackups\AdventureWorks2.bak',
DISK='Z:\SQLServerBackups\AdventureWorks3.bak'
WITH FORMAT,
   MEDIANAME = 'AdventureWorksStripedSet0',
   MEDIADESCRIPTION = 'Striped media set for AdventureWorks2012 database;
GO

I just took backup of Adventureworks database on my local machine and provided media name below screenshot will show you the result.

  Additional question: msdb.dbo.backupmediaset.media_set_id actually
  means backup file id if backup media is disk, it that true?

No they both are different. Below query can easily show it to you. I used media name we just created above in the backup query

select bm.media_set_id,
bs.backup_set_id,
bm.media_family_count,
bm. name,
bm. description
from msdb.dbo.backupmediaset  bm
inner join  msdb.dbo.backupset  bs
ON bm.media_set_id=bs.media_set_id
where bm.name='AdventureWorksStripedSet0'

And the output is

So you can see backup_set_id and media_set_id are different.


mongodb is using most of disk space for journal and not cleaning itself

MongoDB (for MMAP storage engine) will allocate 3 journal files by default at 1GiB each.  That's where your journal related space usage is coming from, but it will not grow unless you have a very high insert rate.

You can start with the smallfiles option and reduce the size to 3 x 128MiB if you wish, but be aware that your data files will also be reduced (to 512MiB each) so there will be many more of them and they will need to be allocated more often when adding data.

As for whether to increase your storage, that depends on how much data you intend to add to the database - it will need to allocate new data files to store any data you insert, so it really is dependent on your planned usage as to whether 10GiB is enough or not.


You can use count(*) over() to make sure your result set has that many rows.

You didn't say what to do if the rows = 10, only > or <, so you may need to change the operand to >= based on what you want.

DB FIDDLE

select *
into mytable
from (select 'X' as c1) x
cross apply (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))as y(Y)

select * from mytable

--change the 10 to 11 to see it not return
select * from
(
select *, COUNT(*) OVER() CT
from mytable
where c1 = 'X'
) sub
where CT > 10


How to mount a query to join by searching by last date

One way to do it is to use CTE (but I haven't tested the syntax myself). For example:

WITH ANSWER AS
(SELECT TB_COSTS.COD_PROD
    , TB_COSTS.DATE_ATT
    , TB_COSTS.COST
    , ROW_NUMBER() OVER (PARTITION BY TB_COSTS.COD_PROD ORDER BY DATE_ATT DESC) AS ORDER_VALUE
FROM TB_COSTS)

SELECT TB_PRODUCTS.ID
, TB_PRODUCTS.COD_PROD
, TB_PRODUCTS.Name
, ANSWER.DATE_ATT
, ANSWER.COSTS
FROM TB_PRODUCTS
INNER JOIN ANSWER on TB_PRODUCTS.COD_PROD = ANSWER.COD_PROD
AND ANSWER.ORDER_VALUE = 1
WHERE TB_COSTS.COST > 0


Join tables in two databases make query slow? is better to partition db?

You've got a bunch of different questions in here, so let's break 'em out individually.

Q: If I join two tables in the same database with the above query, why is it slow?

A: For starters, you're not using a WHERE clause, so SQL Server has to build the complete result set, merging both tables together. If you only need a subset of the data, consider using a WHERE clause to just get the data you need.

Once you've done that, note that you're using a LEFT OUTER JOIN. This tells SQL Server, "Not all of the table1 records will have matching records in table2." That's totally fine if it's true - but if you know all t1 records will have at least one t2 record, use an INNER JOIN instead.

Next, indexing starts to come into play - depending on the width of the tables and the numbers of fields, you may want to add indexes on the fields you're using for the join. To get good advice on that, it's best to post the actual execution plan you're working with.

Q: If I the tables are in different databases on the same SQL Server, does that change anything?

A: No. There's some interesting gotchas around things like default isolation levels in different databases, but for the most part, your queries should produce the same execution plans and speeds.

Q: Should I use table partitioning to make this go faster?

A: You mentioned database partitioning, but there's no such thing in SQL Server - I'm guessing you meant table partitioning. Generally speaking, no, I wouldn't jump to database design changes in order to make a join go faster. Start with the basics - understanding SQL Server execution plans - and only make database design changes to solve problems that you can't fix with things like indexes.


How to INSERT into mySQL without buffering, or force immediate buffer commit?

Plan A:

Shrink innodb_buffer_pool_size so as to force flushing to disk sooner.

Be cautious -- too small (a few MB) a buffer_pool may lead to a hang or crash.
There is still some caching, so you still cannot get precisely what you want.

Plan B:

Insert 1000 rows.
Shutdown MySQL (only this once)
Do the math.
Extrapolate to see how many rows to insert.
Finish to the computed limit.

Caveat:  The math won't be quite right, so you might under/overshoot.

Plan C:

As with Plan B, but do a second shutdown and extrapolation after inserting half the number of rows computed from Plan B.  This should give you a much more accurate target.

Plan D:

From my experience, InnoDB tables are usually 2x to 3x larger than MyISAM tables with the same schema and same data.  Since MyISAM table size is rather easy to compute, will doing that, then multiplying by 2 to 3 be good enough?

If all columns are TEXT, that is a pretty messy schema.  The storage of data will be compromised.  SELECTs will have to work harder, especially for range tests on numeric values stored in TEXT columns.


Is there a way to search the earliest or latest postgres partition for table?

First off, many thanks to those who commented and helped me get to this answer. I was not as detailed as I should have been in posting the question. I believe this is the answer I seek:

select table_name from information_schema.tables where table_name like 'table_%' order by table_name limit 1;

It should return the earliest table partition name given the naming conventions we use on them.


I agree with Markus W Mahlberg that this a very broad question and I am looking forward to read his blog. In my answer below I providing a few guidelines that should provide you with a good starting point when designing your data models.

As as a rule of thumb you should favour embedding, unless you need to regularly query the embedded objects on its own or if the embedded array is likely to get too large(>~100 records). Don't worry about the overhead of joins when splitting up a document into multiple collections as application-level joins are generally comparable in speed compared to a server-side join in a relational database provided your collections are properly indexed.

When normalising you should also take in  consideration the write/read ratio of your data. A field that will mostly be read and only rarely updated is a good candidate for denormalisation. If you denormalise a field that is updated frequently then the extra work of finding and updating all the instances is likely to outweigh the savings that obtained from denormalising.

Looking at your particular case, you are probably better off splitting users and comments in separate collections unless you do not allow users to change their username. You could store the ObjectId of a user in the comments collection to link the two collections. You can then use an application-level join like the one below. In this example query the the 10 most recent comments for that particular user are returned:

 // Fetch the user documents identified by this user_name
> user = db.users.findOne({user_name: "john doe"});// assumes unique index
   // Fetch all the comments that are linked to this user
> comments = db.comments.find({_id: user._id  }).sort({CreatedAtUtc : -1}).limit(5000).toArray() ;


linux mysql case insensitive lower_case_table_names=1 make server cannot be started

Config installation by download file mysql-apt-config_0.8.14-1_all.deb from mysql website (option, if you want to install mysql 8).
Install mysql sudo apt-get install mysql-server.
Open file my.cnf in /etc/mysql, use sudo nano /etc/mysql/my.cnf.
Insert the lower_case_table_names = 1, like bellow:

[mysqld]
lower_case_table_names = 1
Save file my.cnf.
Backup the folder /etc/mysql, like sudo cp -r /etc/mysql /etc/mysql.bak
Remove mysql installation use sudo apt-get remove -y mysql-* and sudo apt-get purge -y mysql-*.
copy back the folder /etc/mysql.bak, like sudo cp-r /etc/mysql.bak /etc/mysql.
back to step 1 and 2. In step 2, the installation will ask, if you keep the config file in folder /etc/mysql or use default from installation. So, I use the config file from previous installation (N).
Finish installation
check file my.cnf. It should be the file with lower_case_table_names = 1, like step 4.

After installation, mysql will read lower_case_table_names = 1.

Hopefully this can help.


Hardware specs for MySQL database with millions of records

Given the above table description, my first order of business would be to get rid of the duplicate index for three reasons:

it would slow down inserts
needlessly bloat the table to about triple the amount on index info
would render either the PRIMARY KEY or the code index useless in EXPLAIN plans

NOTE : A duplicate index is an index that has the same leading columns as another index. You would remove the index if the leading columns has  no additional columns needed by certain queries.

Please run this to discard the duplicate index

ALTER TABLE records DROP INDEX code;

You need to find out how much memory and disk will be need for a given dataset.

Please see my other posts on memory, threading, and InnoDB configurations

Oct 22, 2012 : How large should be mysql innodb_buffer_pool_size?
Sep 12, 2011 : Possible to make MySQL use more than one core?


Find the SQL query that caused an event in the past

This data might still be in the default trace, which can roll over and phase out files based not only on activity but also based on service restarts (so I say it might be there because you may have restarted SQL Server more than 5 or 6 times in three months, even if there hasn't been a lot of activity).

Anyway, it is one of the examples I included in this answer, which I'll repeat a portion of here:

  Question: Who deleted the dbo.EmployeeAuditData table and when?

This will return any DROP events for an object named EmployeeAuditData. If you want to make sure that it only detects DROP events for tables, you can add a filter: ObjectType = 8277 (the full list is documented here). If you want to restrict the search space to a specific database, you can add a filter: DatabaseName = N'db_name'.

DECLARE @path NVARCHAR(260);

SELECT
   @path = REVERSE(SUBSTRING(REVERSE([path]),
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT
  LoginName,
  HostName,
  StartTime,
  ApplicationName
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass = 47    -- Object:Deleted
AND EventSubClass = 1
AND ObjectName = N'EmployeeAuditData'
ORDER BY StartTime DESC;

Please also see the other answer for additional caveats (e.g. details around the lack of recording of the schema of an object that has been dropped, and also the fact that TextData will never be populated - not that it will ever be anything varying far from DROP TABLE dbo.whatever;).


SQL Server 2005: Full-text search, order by matches

You could try FREETEXTTABLE:

SELECT ft.[Rank], p.*
FROM dbo.products AS p
INNER JOIN FREETEXTTABLE(products, productname, 'tennis ball') AS ft
ON ft.key_column = p.key_column
ORDER BY ft.[Rank] DESC;


Is there a way to make this select retrieve the same results with a single seek?

I'd try this but I have no idea if it will be more efficient. You need the DISTINCT to remove duplicates, so the UNION ALL might be more appropriate, no need for two distinct operations:

SELECT DISTINCT
    JobRunId = @JobRunID,
    d.*
FROM dbo.TestUpload
  CROSS APPLY
    (   SELECT
          ThingAName AS Name,
          ThingAType AS [Type],
          ThingAGranularity AS Granularity
      UNION                            -- or UNION ALL
        SELECT
          ThingBName,
          ThingBType,
          ThingBGranularity
    ) AS d
WHERE JobRunId = @JobRunID ;

UNION ALL plan:

UNION plan:


Is that the whole problem statement?  How come AR Clerk, Salesperson, etc are not described anywhere?  You shouldn't just include only the entities listed.  The italicized paragraph contains more entities and processes that should be modeled.
You can't have 2 Customer entities in the diagram.  Draw another line from Customer to Receive Cash if you need to.  You should use a few sentences to define each entity so others know what you intend them to be.


Insert iterated values from one table to another in OracleDB

You can check whether the entry for the user already exists in the table and then exclude that users.

Depending on your data, you can do it with an insert, e.g.

insert into task_group (id, user_id, phone,name, company_no)
select rownum, id, phone,name, 22
  from users u
 where not exists(select 1 from task_group t where t.user_id = u.id);

you cann also loop over the data in your cursor and do commit every x Rows, but it is a very slow way to do that


Chain your insert with data-modifying CTEs:

WITH ins0 AS (
   INSERT INTO siteInfo (siteName, siteHost, notes, ... )
   VALUES ('Sample', 'sample.com', 'note', ... );
   RETURNING siteid
   )
INSERT INTO siteLinks (siteID, URL)
SELECT siteid, 'test'
FROM   ins0;

Or use lastval() or currval():

BEGIN;

INSERT INTO siteInfo (siteName, siteHost, notes, ... )
VALUES ('Sample', 'sample.com', 'note', ... );

INSERT INTO siteLinks (siteID, URL)
VALUES (lastval(), 'test');

COMMIT;

Related:

Reference value of serial column in another column during same INSERT
PostgreSQL multi INSERT…RETURNING with multiple columns


psql: FATAL: Peer authentication failed for user

In a fresh install from a few days ago, the second line of my pg_hba.conf is

local   all             all              peer

I believe this is the one that makes your connection attempt fail.

The order of rules matter here: the first one that matches the access method, username, database name and source IP range will be considered.  If it fails, then there is no second try, so the connection attempt will likely fail.  Or, as the documentation states:

  There is no "fall-through" or "backup": if one record is chosen and the authentication fails, subsequent records are not considered. If no record matches, access is denied.

The solution is easy: either remove the above line if you don't plan to use peer authentication, or move your specific rule above this one.


Yes, it is allowed. Every relation can be related to any other one (or even to itself.)

The naming does not help much but if the created by means the (natural) "ingredients" that an artificial product is composed of, then it makes sense to use a many-to-many relationship.


Include single quote string in redshift dynamic SQL

While the QUOTE_LITERAL() function is helpful in specific contexts, I think you still need to manually escape the single quotes when you use Dynamic SQL.
So your query should follow this:
Execute 'Select *, ID as Main_Id, ' ||
         'schema.func_date(quote_literal(''2020-02-20''),quote_literal(''ST'')), '||
         'from main_table'


Link Server Error - From SQL2008r2 to SQL2014

Please try specifying the @datasrc parameter when you create a linked server, e.g.

EXECUTE sp_addlinkedserver
@server = N'server\SQL2014',
@srvproduct=N'sql_server', @provider=N'SQLNCLI10',
@datasrc = N'server\SQL2014'

The confusion may lie with the @server parameter being a descriptive name of the linked server to use in your code.  @datasrc is actually physical server name used by OLEDB to make the connection.


How to solve deadlocks depends entirely on your data requirements.

Are you content with only applying the last update to the data ?
In that case, don't use a transaction at all - this will happen automatically.

Does every user have to see the real updates being applied for only that user?
This is what you are currently attempting, including (ugh) keeping a transaction open while waiting on a web page, of all things.

I would start with investigating the actual need to do this very bad thing...

Regardless of the ultimate goal, explicit transactions should be used sparsely, wisely, and with good reason.


Work around average behaviour with nulls

To count NULL like 0 when calculating an average, use

avg(coalesce(colname, 0))

coalesce will return the first of its arguments that is not NULL.


How to query multiple tables to see if they share a common column?

One possible way is selecting from information_schema.tables and a correlated subquery from information_schema.columns.

SELECT t.table_name `TABLE _NAME`,
       coalesce((SELECT 'TRUE'
                        FROM information_schema.columns c
                        WHERE c.table_schema = t.table_schema
                              AND c.table_name = t.table_name
                              AND c.column_name = '<column>'),
                'FALSE') `HAS COLUMN`
       FROM information_schema.tables t;

And another is conditional aggregation from information_schema.columns.

SELECT table_name `TABLE NAME`,
       CASE
         WHEN count(CASE column_name
                      WHEN '<column>' THEN
                        1
                    END) > 0 THEN
           'TRUE'
         ELSE
           'FALSE'
       END `HAS COLUMN`
       FROM information_schema.columns
       GROUP BY table_name;


MySQL and PostgreSQL has JSON data types, is it the same as NoSQL database?

A relational DBMS processes data as relations, which is just a fancy way of saying tables consisting of rows and columns. Even if a column's type is defined as JSON, to the DBMS it is still a single column's value. There's an analogy with, say, a column defined as decimal. Externally we may say that column has an integer part, a fractional part, a number of decimal places and so on. To the DBMS, however, it is a single column's value.

The same holds even if code pulls values from within the JSON. These pulled values will be presented to the consumer as columns within a row. This row will be part of a relation (i.e. a table), and every row in that relation will have the same structure. Just because a query's result set is constructed on-the-fly at run-time it is no less a relation than those defined through a CREATE TABLE statement.

A NoSQL DBMS (and here I believe you mean the document DB variety, like MongoDB, rather than key-value or columnstores), however, thinks of data as JSON documents. It accepts well-formed JSON as input and returns JSON in response to queries. This is its model. It sees the world as documents, not tables.

In both models it is the interface between the client and the server that is the distinguishing feature. What each DBMS chooses to do internally is nobody's business but its.


Is Ola Hallengrens Stored Procedure what we need and safe to use

To answer your question, yes Ola Hallengrens scripts are highly respected and safe to use. As much as any 3rd party tools can be. How you choose to use (or mis-use) them are up to you. Make sure you have the current version.

The best advice i could give you if you unsure is to test them yourself somewhere safe and form your own opinion.

Take the time to read the guide and understand the default settings ( They are a good starting point) and then test them yourself.

Although Olas scripts are awesome. They're not a silver bullet. If you have a table hitting 99% fragmentation in one day you have something else going on. Are you shrinking your db daily?


Every table should have a primary key (I really can't think of a reason not to have one). So having a paymentID column to your payment table is definitely a standard design.


You could just add the name of the student in Table B instead of Student_ID but that would not be normalized and you would be duplicating data, also if you don´t have constraints it may be possible for you to add students that are not registered in Table A. Name column for sure will use more bytes than Student_ID. Lookup will be efficient since you´re using Student_ID as clustered index in Table A. If you make queries filtering by that column your search would be fast, SQL Engine will use an index seek operation in the execution plan and even if the table is small SQL will put the entire table in cache (RAM) so it won´t have to go and read from disk. For a transactional app your design is fine, for a data-warehouse you may use a denormalized design where duplicating data is allowed.


How to compare the data type of a query value in Postgresql

You need to convert the string 'interval'  to a type OID:
select pg_typeof(current_timestamp - '2020-01-01 00:00') = 'interval'::regtype;