MySQL Update a table based on the last data from another table while grouping with conditions I need a query to UPDATE a series of fields based on another table

Table lp_plates_backup

|       uid | brand    | model    |
|         1 |          |          |
|         a | Old      | Error    |
|       ... | ...      | ...      |

Table lp_pictures_backup

| plate_uid | brand    | model    | date_validated      |
|         1 | Fiat     | Panda    | 2014-10-11 10:03:20 | < Last one
|         1 | BMW      | 7-Series | 2014-08-04 11:21:18 |
|         1 | BMW      | 7-Series | 2014-07-28 19:14:02 |
|         1 | Mercedes | S-Class  | 2014-06-12 08:54:57 |
|         a | Tesla    | Model S  | 2014-12-17 11:00:00 | < Last one
|         a | BMW      | 3-Series | 2014-11-07 14:34:11 |

I need to update columns brand and model of lp_plates_backup based on the last date_validated entry of table lp_plates_backup while lp_plates_backup.uid match lp_pictures_backup.plate_uid for each lp_plates_backup.uid but only if:

lp_pictures_backup.brand is not empty
lp_plates_backup.brand is different from lp_pictures_backup.brand OR lp_plates_backup.model is different from lp_pictures_backup.model

The result should shown (lp_plates_backup) :

|       uid | brand    | model    |
|         1 | Fiat     | Panda    |
|         a | Tesla    | Model S  |

I have read these posts and made lot of tests but without getting the expected result:

https://stackoverflow.com/questions/1973246/how-to-specify-the-parent-query-field-from-within-a-subquery-in-mysql
https://stackoverflow.com/questions/17038193/select-row-with-most-recent-date-per-user
https://stackoverflow.com/questions/11156163/optimize-sub-query-selecting-last-record-of-each-group

Here is the last query I try:

UPDATE
    lp_plates_backup AS t
    LEFT JOIN (
        SELECT
            plate_uid, brand, model, date_validated
        FROM
            lp_pictures_backup as parent
        WHERE
            brand <> '' AND
            date_validated = (SELECT MAX(date_validated)
                 FROM lp_pictures_backup as t2
                 WHERE t2.plate_uid = parent.plate_uid)
        GROUP BY
            plate_uid
    ORDER BY
        date_validated DESC

) AS m ON
    m.plate_uid = t.uid
SET
    t.brand = m.brand,
    t.model = m.model
WHERE
    t.brand <> m.brand
    OR
    t.model <> m.model

I think I had too much brainstormed and getting confused making useless too complicated code.

Any expert in the place?

Thanks for helping.

The correct query is:

UPDATE
    lp_plates_backup AS t
    INNER JOIN  (
        SELECT
            plate_uid, brand, model, date_validated
        FROM
            lp_pictures_backup as parent
        WHERE
            brand <> '' AND
            date_validated = (
                 SELECT MAX(date_validated)
                 FROM lp_pictures_backup as t2
                 WHERE t2.plate_uid = parent.plate_uid
                 GROUP BY
                     plate_uid)
) AS m ON
    m.plate_uid = t.uid
SET
    t.brand = m.brand,
    t.model = m.model
WHERE
    t.brand <> m.brand
    OR
    t.model <> m.model;

Just some little explanations. You need INNER JOIN because you must update row of lp_plates_backup only if plate_uid exists into lp_plates_backup. ORDER BY is useless because you are selecting all rows, order is not important.

You need the max date_validated of rows grouped by plate_uid, select MAX(date_validate) must individuate only a row for plate_uid through the date_validated field. So you need to add group by to select max(..).

The query select plate_uid returns a row for plate_uid so you don't need to aggregate here. The condition of single row is already builded into select max subquery.

I hope you did not get confused by me :-)

Updated

The previous works well if date_validate is the pair (plate_uid, date_validated) is unique.
If you have this kind of data:

| plate_uid | brand    | model    | date_validated      |
|         1 | Fiat     | Panda    | 2014-10-11 10:03:20 |
|         1 | BMW      | 7-Series | 2014-10-11 10:03:20 |   <- changed data
|         1 | BMW      | 7-Series | 2014-07-28 19:14:02 |
|         1 | Mercedes | S-Class  | 2014-06-12 08:54:57 |
|         a | Tesla    | Model S  | 2014-12-17 11:00:00 |
|         a | BMW      | 3-Series | 2014-11-07 14:34:11 |

The following query returns the first two rows for plate_uid 1.

SELECT plate_uid, brand, model, date_validated
FROM lp_pictures_backup as parent
WHERE
      brand <> '' AND
      date_validated = (
                 SELECT MAX(date_validated)
                 FROM lp_pictures_backup as t2
                 WHERE t2.plate_uid = parent.plate_uid
                 GROUP BY plate_uid)

The update will choice the values from the first or the second row. I think of the following alternatives:

using some other lp_pictures_backup fields to choise between rows with the same date_validated.
enforcing a unique constraint on table. I.e. alter table lp_pictures_backup add unique index (plate_id, date_validated). Rejecting invalid data.
detecting valid lp_pictures_backup pair of (plate_uid, date_validated). Updating lp_plates_backup only with valid pairs, review invalid pairs and correct them.

Maybe there are more alternatives. I prefer enforcing contraint on data so to have better data. I expand 3th alternative. Just create a view to define what a valid pair (plate_uid, date_validated) is:

CREATE VIEW lp_pictures_backup_valid as
SELECT plate_uid, date_validated
FROM lp_pictures_backup as parent
WHERE date_validated = (
        select max(date_validated)
        from lp_pictures_backup t2
        where t2.plate_uid =  parent.plate_uid GROUP BY plate_uid
      )
group by plate_uid, date_validated
having count(*) = 1;                    <-- you can change this to make an invalid row

A valid pair (plate_uid, date_validated) is a pair with the max date_validated only if there are a unique date_validated value.

I rewrite the update statements to consider only valid pair:

UPDATE
    lp_plates_backup AS t
    INNER JOIN  (
        -- modification start
        SELECT p.plate_uid, p.brand, p.model, p.date_validated
        FROM lp_pictures_backup as p
             INNER JOIN lp_pictures_backup_valid valid
                        ON p.plate_uid = valid.plate_uid and
                           p.date_validated = valid.date_validated
        WHERE p.brand <> '')
        -- modification end
    )
    AS m ON m.plate_uid = t.uid
SET
    t.brand = m.brand,
    t.model = m.model
WHERE
    t.brand <> m.brand
    OR
    t.model <> m.model;

Hope this make sense.

Update: 2014-03-20

In the first case:

  Blockquote
   1. using some other lp_pictures_backup fields to choise between rows with the same date_validated.

I have assumed your date is like this:

|id| plate_uid | brand    | model    | date_validated      |
|4 |         1 | Fiat     | Panda    | 2014-10-11 10:03:20 |
|3 |         1 | BMW      | 7-Series | 2014-10-11 10:03:20 |
|2 |         1 | BMW      | 7-Series | 2014-07-28 19:14:02 |
|1 |         1 | Mercedes | S-Class  | 2014-06-12 08:54:57 |
|2 |         a | Tesla    | Model S  | 2014-12-17 11:00:00 |
|1 |         a | BMW      | 3-Series | 2014-11-07 14:34:11 |

You can try this:

UPDATE
    lp_plates_backup AS t
    INNER JOIN  (
        SELECT t1.plate_uid, t1.brand, t1.model, t1.date_validated
        FROM lp_pictures_backup as t1,
          (SELECT t2.plate_uid, MAX(id) as id,  MAX(date_validated) as dv
           FROM lp_pictures_backup as t2
           GROUP BY t2.plate_uid) as t3
        WHERE t1.brand <> '' AND
              t1.plate_uid = t3.plate_uid AND
              t1.date_validated = t3.dv AND
              t1.id = t3.id
) AS m ON
    m.plate_uid = t.uid
SET
    t.brand = m.brand,
    t.model = m.model
WHERE
    t.brand <> m.brand
    OR
    t.model <> m.model;

The fields used to choise the row to be updated are extracted by this part:

 ...
 (SELECT t2.plate_uid, MAX(id) as id,  MAX(date_validated) as dv
  FROM lp_pictures_backup as t2
  GROUP BY t2.plate_uid) as t3
 ...

So it assumes a correlation between date_validated and id: at increasing dates corresponds to increasing id.

Hope it help.


PostgreSQL - Datetime ranges overlap I have a table with datetime fields start and end. And I have a list of (start, end) items. I need to check which items from the list overlap with data in the table.
The current query looks like this:

select br.duration from booking, (
    select tstzrange('2016-09-06 03:45:00+00', '2016-09-06 14:45:00+00') as duration
    union select tstzrange('2016-09-06 14:45:00+00', '2016-09-06 15:45:00+00') as duration
    -- other items from my list
) as br
where tstzrange(start, end) && br.duration

Are there any other ways to do it? Do you think it will work if I have millions rows in the table and will compare them with hundreds items from the list?

I suggest a couple of important improvements for dealing with a million rows:

SELECT br.duration
FROM  (
   VALUES
      ('[2016-09-06 03:45:00+00, 2016-09-06 14:45:00+00)'::tstzrange)
    , ('[2016-09-06 14:45:00+00, 2016-09-06 15:45:00+00)')
      -- more items
   ) br(duration)
WHERE EXISTS (
   SELECT FROM booking
   WHERE  tstzrange(ts_start, ts_end) && br.duration
   );

While providing your list of values with the needlessly verbose and expensive form SELECT ... UNION ..., make that UNION ALL, or Postgres will waste time trying to fold duplicates. And you would only need to declare column name(s) and data type(s) for the first SELECT of the UNION query.
But a VALUES expression is simpler and faster. Or provide an array tstzrange[] and use unnest():

Optimizing a Postgres query with a large IN

The query you have would return one row for every overlapping row in booking, while you probably want each overlapping value from the list once, most likely. You could add DISTINCT or GROUP BY to get unique rows, but that would still be a waste of time. An EXISTS semi-join is one of the much simpler and cheaper alternatives for your case: Each row from duration is returned exactly once if an overlapping entry is found and Postgres can stop looking further for this row.
The query would still be slow without index support. Create a functional GiST or SP-GiST index. The latter probably performing best:

CREATE INDEX booking_ts_range_idx on booking USING spgist (tstzrange(ts_start, ts_end));

Related:

Optimizing queries on a range of timestamps (two columns)
Native way of performing this hours of operation query in PostgreSQL
Help with adding a time filter to Postgres query
Perform this hours of operation query in PostgreSQL (incl. SP-GiST index example and benchmark comparing alternatives)


Calculating difference between consecutive timestamps. Error when i reach the hour mark I am using MySQL

I have a table that contains among other things an id a time in the format date:time (YYYYhhmmss) and a readingvalue. The difference between each reading value is 300 seconds but this may vary slightly so i need to calculate the difference between each timestamp and then add the difference in readingvalue for that allotted time.

Below is what i have tried and this works until i get to the a timestamp of 10:55:00 to 11:00:00. The difference in seconds this gives is not 300 but 2700. Does anyone know how to fix this. Thank you

SELECT
    a.id as id1,
    b.id as id2,
    a.rt1 as rt1,
    b.rt1 as rt2,
    a.avg_readingvalue as arv1,
    b.avg_readingvalue as arv2,
    time_to_sec(b.rt1-a.rt1) as rtdiff,
    b.avg_readingvalue+a.avg_readingvalue as arv21
FROM table1 a
INNER JOIN table1 b
    ON b.id=(a.id+1)
ORDER BY by a.id


Error 1046 Mariadb: No database selected GRANT ALL ON my-database.* TO my-user@10.0.0.1 IDENTIFIED BY 'password';
ERROR 1046 (3D000): No database selected

Ok, I have created a database with a "-" on the name (did the same thing on the user)... then when I try to set the grants on it the database wont work.

If I use the base the error is different

MariaDB [(none)]> use my-base
Database changed
MariaDB [my-base]> GRANT ALL ON 'my-base'.* TO 'my-user'@'10.0.0.1' IDENTIFIED BY 'password';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''my-base'.* TO 'my-user'@'10.0.0.1' IDENTIFIED BY 'password'' at line 1
MariaDB [my-base]>

Try escaping it with backticks?

GRANT ALL ON `my-database`.* TO `my-user`@`10.0.0.1` IDENTIFIED BY 'password';

Also, if you already have a user my-user@10.0.0.1, you don't need to provide the IDENTIFIED BY... portion of your grant statement


Difference Between Sign In and Login (Users and Groups) I am trying to understand a paragraph from my notes, re Users and Groups in Active Directory. The phrase is:

  Users and Groups Sign in, they do not login.

Now, what I (think I) understand is that logins give access to SQL server, Users are given access to specific databases. But I have not been able to find anything that seems reliable (or barely anything) about sign ins. Could someone please explain to me or give me a ref on what signing in is about and how it is different from logins?
Thanks.

I think arguing about login vs sign-in is sematics.

Sql server allows you to log in via 2 modes.

SQL authentication: means using a user id and password which is not the same  as your domain credentials.

Or.

Windows authentication: This can be granted to an individual domain user or a domain group.

Domain groups are a nice way of granting access without having to grant access to individual users each time. For example if a department or team should all have access to a system (e.g HR or Payroll) you can grant access to the team as a whole. As users are added to the team in AD they automatically inherit access to the db via their membership of the group.

This can be overridden by denying individuals access if required. Eg all of HR have access except user xyz.

If SQL is configured to use both modes this referred to as "mixed mode" authentication.

A login gives you access to the SQL  instance. You also need to be granted access to individual dbs. Once you have access to sql the method becomes irrelevant. Once you're in, you're in.

Technically there is third method related to contained databases. But in my experience this is rare.


Get distinct count from row and display in sepearte column I am trying to get count of section type for each section id and course id

My sections table

╔════╤════════════╤═════════╗
║ id │ course_id  │ name    ║
╠════╪════════════╪═════════╣
║ 1  │ 1          │ Quants  ║
╟────┼────────────┼─────────╢
║ 4  │ 1          │ Verbal  ║
╚════╧════════════╧═════════╝

My levels table

╔════╤════════════╤══════════════╤══════╗
║ id │ section_id │ section_type │ name ║
╠════╪════════════╪══════════════╪══════╣
║ 1  │ 1          │ 1            │ aaa  ║
╟────┼────────────┼──────────────┼──────╢
║ 2  │ 1          │ 1            │ bbb  ║
╟────┼────────────┼──────────────┼──────╢
║ 3  │ 1          │ 2            │ ccc  ║
╟────┼────────────┼──────────────┼──────╢
║ 4  │ 1          │ 1            │ ddd  ║
╚════╧════════════╧══════════════╧══════╝

I need the following ouput

╔════╤════════╤════════════════════════╤═════════════════════════╗
║ id │ name   │ count(section_type =1) │ count(section_type = 2) ║
╠════╪════════╪════════════════════════╪═════════════════════════╣
║ 1  │ Quants │ 3                      │ 1                       ║
╚════╧════════╧════════════════════════╧═════════════════════════╝

This is the query i tried

SELECT a.name, a.id, count( DISTINCT b.section_type )
FROM sections a
INNER JOIN levels b ON a.id = b.section_id
WHERE a.course_id =1
GROUP BY b.section_type

The question as I understand it is two-fold:

How to count some value occuring in a separate column
How to autogenerate column list based on result of some query

While 1 is solvable easily, I am quite certain you won't be able to acheive 2.

The solution for 1 could be as following.

Given the test tables and data:

CREATE TABLE sections(
  id INT UNSIGNED PRIMARY KEY,
  course_id INT UNSIGNED,
  name VARCHAR(32));
INSERT INTO sections(id,course_id,name)
VALUES (1,1,'Quants'),
  (4,1,'Verbal');
CREATE TABLE levels(
  id INT UNSIGNED PRIMARY KEY,
  section_id INT UNSIGNED,
  section_type INT UNSIGNED,
  name VARCHAR(32));
INSERT INTO levels(id,section_id,section_type,name)
VALUES (1,1,1,'aaa'),
  (2,1,1,'bbb'),
  (3,1,2,'ccc'),
  (4,1,1,'ddd');

You can get desired output with a:

SELECT a.name, a.id,
  SUM( CASE b.section_type WHEN 1 THEN 1 ELSE 0 END ) as section_type_1,
  SUM( CASE b.section_type WHEN 2 THEN 1 ELSE 0 END ) as section_type_2
FROM sections a INNER JOIN levels b ON a.id = b.section_id
WHERE a.course_id =1
GROUP BY a.course_id;


Best design for a tree-like structure database I'm designing a database in MS Access which is used for tracking the locations of a variety of chemical solutions, made in batches.

Each batch can be split (aliquoted) practically indefinitely. That is, we may manufacture 100g of a product, which is aliquoted into two 50g vials. One is kept in storage, the other is shipped somewhere else and further split into 50 1g vials. These vials may be transported at any time, and may be aliquoted further.

I would like to be able to track the history of any particular vial. Where it has been (and when), including the history of its 'parent' vial and the parents of that.

That is, if I look at the history of vial #7 in the above image, I want to see the history of vial #7, (reverse chronologically, for example), followed by the histories of vials #6, #3 and #8.

I'm struggling to find the best way to record this data; currently I have one main table (plus several lookup tables) which has one line for each 'node'. A node represents a vial being in a location; so each line has an AutoNumber primary key, a 'parent' (the primary key of the previous node - either the previous location of the vial, or the parent vial if it was aliquoted), a 'location' field, the batch number etc.

There is already redundancy here because the batch number is on each node whereas it really only needs to be on the first node after manufacture (it clearly doesn't change when the batch is aliquoted).

So the data structure is the first issue here.

Once I've got the data structure, does anyone have any ideas for how best to show historical data for a vial?

Many thanks

I would create a separate table for history.  If it was me, I would use an auto-increment column for the primary key (historyID) , a column for the vial ID, and a column for a location ID.   I would think the vial ID would be foreign key to the nodes table. You could then query against a vial to see it's full history.

Since you don't list what your lookup tables are, I am going going to assume you don't have a history table yet.  I am also not sure you have a separate location table, and if you don't I would also use a location table that simply has a autoincrement primary key and a location description  the primary key of the location table would have foreign keys in both your node table and the history table.

Since you mention that the batch number is redundant, I would also add a separate batch table that uses auto-increment as the primary key (batchID) along with any columns that would be useful for a batch, (date of manufacture,other information unique to the batch, etc.)  The batchID would would then be a foreign key in your node table and although the batch ID might be repeated many times for different nodes, you might be able to remove other redundancies related to the batch in your node table.  Of course, since you don't list all the columns that are in your node table, it's difficult to give advice that is not based on some assumptions.  As I review my post, I would also put the batchID in the history table.  It would give you an easy way to trace the history of the parent batch.

Basically, you want to go through the process of normalizing your database.  By the time you normalize, your first main nodes table might end up being a very simple table that provides for the relationships between parent and children vials in a single table.  (Some might advocate for a separate table of parent and children vials, but since your manufacturing methods allow for multiple generations, I would keep it in a single table, and keep it simple.)

Sounds like an interesting project.

I hope this all makes sense and is helpful.

Although I have been involved with databases since the days when Access 97 was still current, this is my first post in dba.stackexchange.  I've been the accidental DBA for multiple projects at our company and I am finally finding some of the most commonly used resources for help and information on the web DBAs.


Sql_text Data Missing When Querying Extended Event File Target Using TSQL I set up an Extended Events session to capture SQL Server error events.
Below is the TSQL I used to create the XE session:
CREATE EVENT SESSION [ErrorCapture] ON SERVER
ADD EVENT sqlserver.error_reported(
    ACTION(sqlserver.client_hostname
    ,sqlserver.database_id
    ,sqlserver.sql_text
    ,sqlserver.username)
    WHERE ([severity]>=(11)))
ADD TARGET package0.event_file(SET filename=N'D:\ErrorCapture.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

Using SSMS, I am able to view all of the available event data, including sql_text.  However, when using TSQL to query the event file, data for the sql_text action shows up as NULL.  Below is the query used.  Any thoughts on why this might be?
with ErrorCaptureEventData as
(
  select convert(xml, event_data) as EventData
  from sys.fn_xe_file_target_read_file(N'D:\ErrorCapture*.xel', NULL, NULL, NULL)
)
select EventData.value('(event/@timestamp)[1]', 'datetime2(7)') as TimeStamp
       ,EventData.value('(event/data[@name="error_number"]/value)[1]', 'int') AS [Error_Number]
       ,EventData.value('(event/data[@name="message"]/value)[1]', N'nvarchar(max)') AS [Error_Message]
       ,EventData.value('(event/data[@name="sql_text"]/value)[1]', N'nvarchar(max)') AS SQL_Text
       ,EventData.value('(event/data[@name="statement"]/value)[1]', N'nvarchar(max)') AS SQL_Statement
       ,EventData.value('(event/data[@name="severity"]/value)[1]', N'nvarchar(max)') AS Severity
       ,DB_NAME(EventData.value('(event/action[@name="database_id"]/value)[1]', N'nvarchar(max)')) AS [DB_Name]
       ,EventData.value('(event/action[@name="username"]/value)[1]', N'nvarchar(max)') AS [User_Name]
 from ErrorCaptureEventData
 order by timestamp desc

You're parsing the XML wrong. Since sql_text is an action, you need to do this:
value('(action[@name="sql_text"]/value)[1]', 'NVARCHAR(MAX)')


Informix - dbaccess vs dbexport to export tables to text files Currently, we are using the dbaccess utility to export all tables in the Informix database to text files.  These are used to "mirror" the database multiple Windows machines running MSSQL Server on a nightly basis.

I was looking into the dbexport utility to see if it would be a viable solution.  The text export takes almost 2 hours and I'm looking to shrink that time down if possible.  However, I'm not familiar with these Informix utilities and we do not have a test environment for this... so I'm wondering what exactly is the output from dbexport?  Is there an option to export each individual table to a text file with dbexport?  Thank you.

Using dbexport has similarities, advantages and disadvantages compared to dbaccess.

With DB-Access, you are presumably generating the list of UNLOAD statements that will copy the data to file.  You probably don't lock the entire database, so changes can occur while you're unloading the data, which could lead to inconsistencies.

If you use DB-Export instead, then it automatically locks the database (so the unloaded data will be consistent), and generates both the schema and unloaded data files using the same format the DB-Access generates.  The advantage is you don't have to work so hard.

The other part of the game is DB-Import (dbimport).  It expects to create a database (you can choose the name) and load it from the exported data.

Neither DB-Export nor DB-Import has support for parallelism; both operate on one table at a time.  With DB-Access, you could arrange for de facto parallelism (by using multiple invocations of DB-Access to unload or load the data into multiple tables).

With DB-Access, you can control which tables are exported.  With DB-Export, all tables are exported.

With DB-Export and DB-Import, you don't have to fret about creating and loading the tables in a correct sequence; with DB-Access, you have to think about ensuring that the data is loaded in a correct sequence.

On the whole, DB-Export plus DB-Import is simpler than using DB-Access, but DB-Access is ultimately more flexible.


SQL memory usage with freetext indexing Does full text search and freetext indexing drastically increase the memory usage of a SQL Server database instance?

Today, on my development machine, I installed the fulltext feature on a SQL Server 2014 instance and created a catalog on one of my tables with a fulltext index. I set the index to track changes automatically. The table I used it on has about 5000 records.

Next, I started testing this feature by running some queries. The last query that I've run so far is the following:

SELECT
    'Title' AS [MatchFrom],
    [ShopItem].[Title],
    [ShopItem].[Description],
    [SubCategory].[Name] AS [SubCategory],
    [FreeTextTable].[Rank]
FROM
    FREETEXTTABLE([ShopItem], [Title], 'Paper') AS [FreeTextTable]
    INNER JOIN [ShopItem]
        ON [FreeTextTable].[Key] = [ShopItem].[Id]
    INNER JOIN [SubCategory]
        ON [ShopItem].[SubCategoryId] = [SubCategory].[Id]
UNION
SELECT
    'Description' AS [MatchFrom],
    [ShopItem].[Title],
    [ShopItem].[Description],
    [SubCategory].[Name] AS [SubCategory],
    [FreeTextTable].[Rank]
FROM
    FREETEXTTABLE([ShopItem], [Description], 'paper') AS [FreeTextTable]
    INNER JOIN [ShopItem]
        ON [FreeTextTable].[Key] = [ShopItem].[Id]
    INNER JOIN [SubCategory]
        ON [ShopItem].[SubCategoryId] = [SubCategory].[Id]
UNION
SELECT
    'Subcategory',
    [ShopItem].[Title],
    [ShopItem].[Description],
    [SubCategory].[Name],
    [FreeTextTable].[Rank]
FROM
    FREETEXTTABLE([SubCategory], [Name], 'Paper') AS [FreeTextTable]
    INNER JOIN [SubCategory]
        ON [FreeTextTable].[Key] = [SubCategory].[Id]
    INNER JOIN [ShopItem]
        ON [ShopItem].[SubCategoryId] = [SubCategory].[Id]
ORDER BY
    [FreeTextTable].[Rank] DESC

It was at this point that my computer warned me that it's low on memory:

I quickly realised that the SQL server instance was using an unusual amount of memory (it's moving between 6 gigabytes and 9 gigabytes)

Now I'm quite concerned that implementing any freetext functionality on my live system would cause a strain on my server (a cloud VM that currently has a total of 6GB RAM), but I don't know how to confirm exactly what it is that's using so much memory. I'm currently a solo developer on a new project without any database administrators.

EDIT:

Memory usage still increasing:

SQL version (this is on my development machine running Windows 10 64bit, not the server running my application)

  Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
      Feb 20 2014 20:04:26
      Copyright (c) Microsoft Corporation
      Developer Edition (64-bit) on Windows NT 6.3  (Build 14393: )

The memory usage next day:

As Shanky implied in the comments, your Max Server Memory value is probably not changed from the default. SQL is designed to use everything at its disposal.

I've written a script, based off work from Jonathan Kehayias of SQLskills, that will let you know the value you should use.

Jon advises reserving the following amount of RAM for the operating system:

1 GB of RAM for the OS
plus 1 GB for each 4 GB of RAM installed from 4 – 16 GB
plus 1 GB for every 8 GB RAM installed above 16 GB RAM

Anything left over from that, can be allocated to SQL Server, assuming it's a dedicated instance.

If your VM only has 6 GB available, the recommended value is 3 GB. To change it, you would write something like this (or change it in the Management Studio UI):

EXEC sp_configure 'show advanced', 1;
EXEC sp_configure 'max server memory (MB)', 3072;
RECONFIGURE WITH OVERRIDE;
GO


How to optimize query by preventing repetitive subquery I have query that uses a subquery (select min/max(date) from stage.stage_net_subscription_010_filter_and_net_pds) to find min and max dates of a table.
It seems that the subqueries are repeated very often.

Is there a way to rewrite the query without CTEs such that the subqueries do not get executed for every loop
One other option to speed up the query is see, would be to use generate series in stead of selecting the dates from a table with dates. Any clue if that will make a difference?

Query:
SELECT
         dim_date.date AS date
         , stage_net_subscription_020_classes.client_id AS client_id
         , stage_net_subscription_020_classes.product_id AS product_id
  FROM star.dim_date dim_date
  CROSS JOIN stage.stage_net_subscription_020_classes stage_net_subscription_020_classes
  where date >= (select min(date) from stage.stage_net_subscription_010_filter_and_net_pds)
  and   date <= (select max(date) from stage.stage_net_subscription_010_filter_and_net_pds);

You can use a lateral cross join, although I don't understand why you want to avoid a CTE:
SELECT dim_date.date AS "date",
       stage_net_subscription_020_classes.client_id AS client_id,
       stage_net_subscription_020_classes.product_id AS product_id
FROM star.dim_date dim_date
  CROSS JOIN stage.stage_net_subscription_020_classes
  CROSS JOIN LATERAL (
    select min(date) as min_date, max(date) as max_date
    from stage.stage_net_subscription_010_filter_and_net_pds
  ) as mm
  where date >= mm.min_date
  and   date <= mm.max_date


Can't set default_schema_name for Windows User Group? When I try to set the default schema for my Windows Role/Group for Windows Server 2008 R2, it won't allow me to do so...I run:

ALTER USER [UCPSAR\Service Provision] WITH DEFAULT_SCHEMA=[ServiceProvision_Schema]

And I get the error message:

Msg 15259, Level 16, State 2, Line 1
The DEFAULT_SCHEMA clause cannot be used with a Windows group or with principals mapped to certificates or asymmetric keys.

If I do a select to find out why

SELECT name, type_desc, default_schema_name
FROM sys.database_principals
WHERE type in ('S', 'U', 'G');

I end up with default_schema_name for my WINDOWS_GROUP being NULL

Is there any way to remedy this?

Upgrade to a modern version of SQL Server. As the error states, you cannot specify a default schema for a group. You can in SQL Server 2012 and newer.


LIKE to select the independent existence of the word whereever in the text I have a table with the following texts and the key word i am searching for is 'Search'.So i have written a query

SELECT [ID]
  ,[TextValue]
FROM
   [dbo].[SearchLike]
WHERE
   [TextValue] LIKE '%Search%'

Q.1 How can i modify the query so that only the records having 'Search' in the text is returned and it shouldn't be taking 'LSearch'. i.e as per the image first three records only to be returned.?

Using LIKE

Single line like search:

WHERE
 ' ' + [TextValue] + ' ' LIKE '%[.,;:() ]Search[.,;:() ]%'

/* [] contains list of allowable characters, adding spaces around
   [TextValue] removes need to have multiple OR [TextValue] LIKE */

Without padding you will need to specifically handle string appearing at start/end of string:

WHERE
    [TextValue] LIKE '%Search%' --middle
    OR
    [TextValue] LIKE 'Search%'   --start
    OR
    [TextValue] LIKE '%Search'   --end

Using FULLTEXT INDEX

Edit2, for the full text purists

CREATE FULLTEXT INDEX on MSDN states for the AUTO option (the default)

  Although changes are propagated automatically, these changes might not be reflected immediately in the full-text index.

So, it may not give correct results. But then a few seconds later it will.

Also, for upto a few 10k rows it will perform adequately: it scales O(n). I use the LIKE on a table with around 25k rows but users know it will perform badly if they search this way (it's on an "advanced" page). I gain in the trade off by managing a full text index

Full text search isn't the correct solution, it's one option

Note: Experience has shown me that FullText indexing in SQL Server has severe performance implications, ie: Only implement if you really need to and you know what you're doing! (@Andrew Bickerton)


ENABLE_BROKER vs NORECOVERY I have two nodes with MSSQL2012 which work with AlwaysOn High Availability

After some crash I had to remove db from availability group on the one of the nodes. For restoration I've made backup (full+log) of db on the second node and restored it on the crashed one according to standard procedure. Seemed totally ok, but I've faced that when I give primary role to restored node then  the application service (which uses Service Broker) doesn't do anything.

sys.databases said that for my db is_broker_enabled=1, but I've found several articles about broker on restored db must explicitly be enabled.

SET ENABLE_BROKER failed due to AlwaysOn.

Internet says that the only way to activate service broker is to restore db again with ENABLE_BROKER option. But for adding db to AlwaysOn I must restore it with NORECOVERY.

Trying to run

RESTORE DATABASE [dbname]
FROM  DISK = N'D:\111.bak'
WITH  NORECOVERY, ENABLE_BROKER

I've seen notification about conflict between these two WITH options

Does anybody know correct way to make such restore? db is over 150GB and it always takes over two hours to check each versions:)

thanks in advance

Yes, using ENABLE_BROKER in the RESTORE conflicts with NORECOVERY. If your database is in NORECOVERY you'll need to wait until it is recovered before you renable the broker. If you are having trouble, try renabling with rollback immediate:

ALTER DATABASE db_name SET ENABLE_BROKER with rollback immediate


Unusual behaviour of a query when using case expression in group by, some records are being missed I have 4 table with below structure:
Table "Customer_Info"
 create table Customer_Info
(
  Customer_Number         int,
  Customer_Issued_Branch  int,
  <Other Columns related to customer>
)

Example Data:
  Insert into Customer_Info(Customer_Number,Customer_Issued_Branch)
  values (1,1001),(2,1002),(3,1003),(4,1001)

Table "Customer_Transaction_Info"
create table Customer_Transaction_Info
(
  Customer_Number    int,
  Channel_Number     int,
  Transaction_Count  int,
  <Other columns related to customer transactions >
)

Example data:
Insert into Customer_Transaction_Info(Customer_Number,Channel_Number,Transaction_Count)
Values(1,5,2),(1,6,1),(1,3,1),(1,1,1),(1,2,1),(2,5,1),(2,1,1)

Table "Customer_Channel_Info"
create table Customer_Channel_Info
(
  Customer_Number         int,
  Channel_Number          int,
  Channel_Branch_code     int
)

Example data:
insert into Customer_Channel_Info (Customer_Number,Channel_Number,Channel_Branch_code)
values (1,5,1001),(1,6,1002),(1,3,1003),(2,5,1004),(3,6,1005),(4,3,1006)

Table "Branch_Info"
create table Branch_Info
(
  Branch_Code      int,
  New_Branch-Code  int
)

Example Data :
insert into Branch_Info(Branch_Code,New_Branch-Code)
values(1001,1010),(1002,1010),(1003,1010),(1004,1010),(1005,1010),(1006,1010)

The problem is that everything seems fine until this part of my query on t4.Branch_Code = t2.Channel_Branch_code. what I mean is that the select * of below query, without group by and sum(), gives me 7 records, but as soon as I add group by part and sum, the below query returns 4 records! It's wired and I do not understand the reason! both case statements,in group part and select part of the query are exactly the same so why some record are being missed?
My Query is :
select t1.Customer_Number,
       case
         when t1.Channel_Number in (1, 2) then t3.Customer_Issued_Branch
         else t4.New_Branch-Code
       end brncode,
       sum(case
             when t1.Channel_Number = 1 then t1.Transaction_Count
             else 0
           end) Tel_Count
  from Customer_Transaction_Info t1
  left join Customer_Channel_Info t2
    on t1.Customer_Number= t2.Customer_Number
   and t1.Channel_Number = t2.Channel_Number
  left join Customer_Info t3
    on t3.Customer_Number = t1.Customer_Number
  left join Branch_Info t4
    on t4.Branch_Code = t2.Channel_Branch_code
 group by t1.customer_num,------------------------>/*This part*/
          case
            when t1.Channel_Number in (1, 2) then
             t3.cust_branch_cod
            else
             t4.New_Branch
          end

Thanks in advance

This behaviour is directly related to your group by - it is behaving as intended, by 'uniquing' your customer_nums (of which there are 4 in your example data).
Without further information on what you are looking to derive with this query it's hard to determine what results you wish for from this query. If you do need any further help, happy to give any guidance that I can.
See the full explanation of GROUP BY on https://www.w3schools.com/sql/sql_groupby.asp.


Optimizing MySQL query with some LEFT OUTER JOINS I have a classifieds website and want to optimize the most intensive query in my server. I don´t have so much experience with MySQL (or any other DB).

This is the complete listing ads:

SELECT
    `category`.`id` AS `category_id`,
    `category`.`subcat` AS `category_name`,
    `category`.`desc` AS `category_desc`,
    `category`.`name` AS `category_pretty_name`,
    `ads`.`id`,
    `ads`.`header`,
    `ads`.`price`,
    `ads`.`oldprice`,
    `ads`.`sellfast`,
    `ads`.`foto1`,
    `ads`.`subcat_id`,
    `ads`.`updated`,
    `ads`.`created`,
    `ads`.`name`,
    `ads`.`phone`,
    `ads`.`hits`,
    `rating_ad`.`num_rates` AS `num_rates`,
    `rating_ad`.`average_rating` AS `average_rating`,
    SUBSTR(ads.body, 1, 160) AS body,
    `ads`.`user_id`,
    `ads`.`hash`,
    `ads`.`hidden`,
    `ads`.`email`,
    `promo`.`promotype` AS `promo_type`,
    `promo`.`end_promo`,
    `supercategory`.`name` AS `supercategory_name`,
    `supercategory`.`id` AS `supercategory_id`
FROM
    `ads`
JOIN `category` ON `category`.`id` = `ads`.`subcat_id`
JOIN `supercategory` ON `supercategory`.`id` = `category`.`cat`
LEFT OUTER JOIN `promo` ON `promo`.`ads_id` = `ads`.`id`
LEFT OUTER JOIN (
    SELECT
        ad_id,
        TRUNCATE (AVG(rating), 2) AS average_rating,
        COUNT(user_id) AS num_rates
    FROM
        ads_rating
    GROUP BY
        ad_id
    HAVING
        COUNT(user_id) >= 1
) AS rating_ad ON `rating_ad`.`ad_id` = `ads`.`id`
WHERE
    `ads`.`sellfast` = '1'
AND `ads`.`subcat_id` = '19'
AND `recycle_bin` = 0
AND `hidden` = 0
ORDER BY
    `promo_type` DESC,
    `updated` DESC
LIMIT 100;

When I do the EXPLAIN Query, this is the result:

So, I see in the Slow queries logs this is consuming about 0.350 secs. Is this optimizable?

This is the SHOW CRETE TABLE:

CREATE TABLE `ads` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `subcat_id` mediumint(2) NOT NULL,
  `province_id` mediumint(2) NOT NULL,
  `price` float(12,2) NOT NULL,
  `oldprice` float(12,2) NOT NULL,
  `type` enum('1','2','3') NOT NULL,
  `header` varchar(200) CHARACTER SET latin1 NOT NULL,
  `body` text CHARACTER SET latin1 NOT NULL,
  `email` varchar(150) CHARACTER SET latin1 NOT NULL,
  `public_email` varchar(150) NOT NULL,
  `name` varchar(50) CHARACTER SET latin1 NOT NULL,
  `phone` varchar(30) CHARACTER SET latin1 NOT NULL,
  `user_id` int(6) NOT NULL,
  `created` datetime NOT NULL,
  `foto1` varchar(150) CHARACTER SET latin1 DEFAULT NULL,
  `foto2` varchar(150) CHARACTER SET latin1 DEFAULT NULL,
  `foto3` varchar(150) CHARACTER SET latin1 DEFAULT NULL,
  `foto4` varchar(150) CHARACTER SET latin1 DEFAULT NULL,
  `foto5` varchar(150) CHARACTER SET latin1 DEFAULT NULL,
  `updated` datetime NOT NULL,
  `hash` varchar(32) CHARACTER SET latin1 NOT NULL,
  `hits` int(6) NOT NULL,
  `new` tinyint(1) DEFAULT 0,
  `sellfast` tinyint(1) DEFAULT 0,
  `website` varchar(250) CHARACTER SET latin1 DEFAULT NULL,
  `tags` varchar(250) DEFAULT NULL,
  `brand_id` smallint(2) NOT NULL,
  `hidden` smallint(1) NOT NULL,
  `recycle_bin` smallint(1) NOT NULL,
  PRIMARY KEY (`id`,`hash`),
  KEY `header` (`header`) USING BTREE,
  KEY `tags` (`tags`) USING BTREE,
  KEY `phone` (`phone`) USING BTREE,
  KEY ` subcat_id` (`subcat_id`) USING BTREE,
  KEY `province_id` (`province_id`) USING BTREE,
  KEY `hidden` (`hidden`) USING BTREE,
  KEY `recycle_bin` (`recycle_bin`) USING BTREE,
  KEY `price` (`price`) USING BTREE,
  KEY `email` (`email`) USING BTREE,
  KEY `created` (`created`) USING BTREE,
  KEY `updated` (`updated`) USING BTREE,
  KEY `user_id` (`user_id`),
  KEY `new` (`new`),
  KEY `sellfast` (`sellfast`)
) ENGINE=MyISAM AUTO_INCREMENT=839759 DEFAULT CHARSET=utf8

Which task most do to made it?

Please provide SHOW CREATE TABLE; it is important for optimization to know, for example, which table recycle_bin is in.

Even before looking at the joins, get rid of the "index merge intersect" by building a "composite" index:

 INDEX(sellfast, subcat_id, recycle_bin, hidden)  -- in any order

A LEFT OUTER JOIN (aka LEFT JOIN), per se, is not the villain.  Having a subquery in it may be the real villain.  Several things can be done with such, but I am not sure any cure applies to your case:

Get rid of LEFT.  But this would lose some rows if they are missing from ads_rating.  Without LEFT, the subquery would probably be performed first instead of repeatedly.  (If this is valid, try it first.)
Move the subquery into the SELECT.  But there are two columns being provided, so it is unlikely to be practical.
Use WITH.  But this requires version 8.0.  What version are you using?
CREATE TEMPORARY TABLE for the subquery, then use it in place of the subquery.  (This may be the best choice.)


What happens in PostgreSQL checkpoint? Here's part of my checkpoint log:

2014-03-26 11:51:29.341 CDT,,,18682,,532854fc.48fa,4985,,2014-03-18 09:15:24 CDT,,0,LOG,00000,"checkpoint complete: wrote 15047 buffers (1.4%); 0 transaction log file(s) added, 0 removed, 30 recycled; write=68.980 s, sync=1.542 s, total=70.548 s; sync files=925, longest=0.216 s, average=0.001 s",,,,,,,,,""
2014-03-26 11:56:05.430 CDT,,,18682,,532854fc.48fa,4987,,2014-03-18 09:15:24 CDT,,0,LOG,00000,"checkpoint complete: wrote 16774 buffers (1.6%); 0 transaction log file(s) added, 0 removed, 31 recycled; write=72.542 s, sync=17.164 s, total=89.733 s; sync files=885, longest=3.812 s, average=0.019 s",,,,,,,,,""
2014-03-26 12:01:21.650 CDT,,,18682,,532854fc.48fa,4989,,2014-03-18 09:15:24 CDT,,0,LOG,00000,"checkpoint complete: wrote 14436 buffers (1.4%); 0 transaction log file(s) added, 0 removed, 33 recycled; write=122.350 s, sync=5.212 s, total=127.676 s; sync files=924, longest=3.740 s, average=0.005 s",,,,,,,,,""
2014-03-26 12:06:25.028 CDT,,,18682,,532854fc.48fa,4991,,2014-03-18 09:15:24 CDT,,0,LOG,00000,"checkpoint complete: wrote 13277 buffers (1.3%); 0 transaction log file(s) added, 0 removed, 29 recycled; write=126.217 s, sync=5.733 s, total=131.991 s; sync files=894, longest=1.859 s, average=0.006 s",,,,,,,,,""
2014-03-26 12:10:41.958 CDT,,,18682,,532854fc.48fa,4993,,2014-03-18 09:15:24 CDT,,0,LOG,00000,"checkpoint complete: wrote 20765 buffers (2.0%); 0 transaction log file(s) added, 0 removed, 28 recycled; write=88.015 s, sync=10.818 s, total=98.872 s; sync files=881, longest=2.690 s, average=0.012 s",,,,,,,,,""

I noticed that sometimes our database is very slow - you can see a very large number of normally short queries stuck for much longer than now. It happens regularly without a clear culprit.

Question: Could checkpoint cause this? What happens in the "sync" phase of checkpoint?

During its operation, PostgreSQL records changes to transaction log files, but it doesn't immediately flush them to the actual database tables. It usually just keeps the changes in memory, and returns them from memory when they are requested, unless RAM starts getting full and it has to write them out.

This means that if it crashes, the on disk tables won't be up to date. It has to replay the transaction logs, applying the changes to the on-disk tables, before it can start back up. That can take a while for a big, busy database.

For that reason, and so that the transaction logs do not keep growing forever, PostgreSQL periodically does a checkpoint where it makes sure the DB is in a clean state. It flushes all pending changes to disk and recycles the transaction logs that were being used to keep a crash recovery record of the changes.

This flush happens in two phases:

Buffered write()s of dirty shared_buffers to the tables; and
fsync() of affected files to make sure the changes really hit disk

Both of those can increase disk I/O load. Contention caused by these writes can slow down reads, and can also slow down flushing of WAL segments that's required in order to commit transactions.

It's been a longstanding challenge, but it's getting worse as we see systems with more and more RAM so they can buffer more data and take longer to write it out. There's discussion between the Linux and PostgreSQL communities on how to deal with this at the moment, as discussed in this LWN.net article. (LWN.net won't be able to keep writing this sort of great work if people don't subscribe. I'm a subscriber and sharing this link because it's useful and informative. Please consider subscribing if you want to see more of this sort of thing.)

The main thing you can do to reduce the impact of checkpoints at the moment is to spread checkpoint activity out by increasing checkpoint_completion_target so that more of the data has been written out by the time the final checkpoint arrives. This has a cost, though - if you update a page (say) ten times, it might be written to disk multiple times before the checkpoint with a high completion target, even though it only strictly had to be written out once for crash safety. A higher completion target makes for smoother I/O patterns but more overall I/O overhead.

The other thing you can do to help is tell your operating system to immediately start writing data when it gets buffered writes. This is like the kernel side of setting checkpoint_completion_target and has a similar trade-off. See the linux vm documentation, in particular dirty_background_bytes, dirty_background_ratio, dirty_expire_centisecs.


Import CSV or JSON file into DynamoDB I have 1000 CSV files. Each CSV file is between 1 and 500 MB and is formatted the same way (i.e. same column order). I have a header file for column headers, which match my DynamoDB table's column names. I need to import those files into a DynamoDB table. What's the best way / tool to do so?

I can concatenate those CSV files into a single giant file (I'd rather avoid to though), or convert them into JSON if needed. I am aware of the existence of BatchWriteItem so I guess a good solution would involve batch writing.

Example:

The DynamoDB table has two columns: first_name, last_name
The header file only contains:  first_name,last_name
One CSV file looks like

:

John,Doe
Bob,Smith
Alice,Lee
Foo,Bar

In the end I coded a Python function import_csv_to_dynamodb(table_name, csv_file_name, colunm_names, column_types) that imports a CSV into a DynamoDB table. Column names and column must be specified. It uses boto, and takes a lot of inspiration from this gist. Below is the function as well as a demo (main()) and the CSV file used. Tested on Windows 7 x64 with Python 2.7.5, but it should work on any OS that has boto and Python.

import boto

MY_ACCESS_KEY_ID = 'copy your access key ID here'
MY_SECRET_ACCESS_KEY = 'copy your secrete access key here'

def do_batch_write(items, table_name, dynamodb_table, dynamodb_conn):
    '''
    From https://gist.github.com/griggheo/2698152#file-gistfile1-py-L31
    '''
    batch_list = dynamodb_conn.new_batch_write_list()
    batch_list.add_batch(dynamodb_table, puts=items)
    while True:
        response = dynamodb_conn.batch_write_item(batch_list)
        unprocessed = response.get('UnprocessedItems', None)
        if not unprocessed:
            break
        batch_list = dynamodb_conn.new_batch_write_list()
        unprocessed_list = unprocessed[table_name]
        items = []
        for u in unprocessed_list:
            item_attr = u['PutRequest']['Item']
            item = dynamodb_table.new_item(
                    attrs=item_attr
            )
            items.append(item)
        batch_list.add_batch(dynamodb_table, puts=items)

def import_csv_to_dynamodb(table_name, csv_file_name, colunm_names, column_types):
    '''
    Import a CSV file to a DynamoDB table
    '''
    dynamodb_conn = boto.connect_dynamodb(aws_access_key_id=MY_ACCESS_KEY_ID, aws_secret_access_key=MY_SECRET_ACCESS_KEY)
    dynamodb_table = dynamodb_conn.get_table(table_name)
    BATCH_COUNT = 2 # 25 is the maximum batch size for Amazon DynamoDB

    items = []

    count = 0
    csv_file = open(csv_file_name, 'r')
    for cur_line in csv_file:
        count += 1
        cur_line = cur_line.strip().split(',')

        row = {}
        for colunm_number, colunm_name in enumerate(colunm_names):
            row[colunm_name] = column_types[colunm_number](cur_line[colunm_number])

        item = dynamodb_table.new_item(
                    attrs=row
            )
        items.append(item)

        if count % BATCH_COUNT == 0:
            print 'batch write start ... ',
            do_batch_write(items, table_name, dynamodb_table, dynamodb_conn)
            items = []
            print 'batch done! (row number: ' + str(count) + ')'

    # flush remaining items, if any
    if len(items) > 0:
        do_batch_write(items, table_name, dynamodb_table, dynamodb_conn)

    csv_file.close()

def main():
    '''
    Demonstration of the use of import_csv_to_dynamodb()
    We assume the existence of a table named `test_persons`, with
    - Last_name as primary hash key (type: string)
    - First_name as primary range key (type: string)
    '''
    colunm_names = 'Last_name First_name'.split()
    table_name = 'test_persons'
    csv_file_name = 'test.csv'
    column_types = [str, str]
    import_csv_to_dynamodb(table_name, csv_file_name, colunm_names, column_types)

if __name__ == "__main__":
    main()
    #cProfile.run('main()') # if you want to do some profiling

test.csv's content (must be located in the same folder as the Python script):

John,Doe
Bob,Smith
Alice,Lee
Foo,Bar
a,b
c,d
e,f
g,h
i,j
j,l


Capturing change deltas when database replication or CDC breaks The company I work for is in the process of implementing change data capture on a Microsoft BI stack for the purpose of capturing incremental changes that need to be loaded into the data warehouse. So far this has worked great for us, we have the database replication setup and table level CDC setup on the replicated database for the specific tables and columns we need to track. The problem that we are running into is that both replication and CDC have the potential to break. When this happens any changes that occur from the time it breaks to the time it is resolved will not be captured and thus cannot be passed to the data warehouse.

I'm wondering what the best practice is to identify all the deltas and get them loaded into the data warehouse? In theory you can rebuild your entire data warehouse but then what is the best way to keep historical Type 2 data?

Assuming that there was a serious failure which causes you to need to 'reconstitute' the missing type 2 changes, you may be able to use your source database data and log backups. If you have preserved these backups you can do something like this process outline:

Restore a copy of the source database to a point just prior to when you experienced the failure. Name the database [dbnameEmergency].
Restore the logs incrementally to [dbnameEmergency], such as 1 minute, 5 minutes, 30 minutes, according to how finely you want to process the deltas in order to reduce the damage.
Reading and comparing the incremental restores will require either creating snapshots, if you have Enterprise edition, or restoring the log to STANDBY mode so as to read the data.
Compare the latest readable changes in [dbnameEmergency] against the state of the [dbnameReplica] (which is the database you are trying to set straight) and write code to apply the changes you are able to infer to [dbnameReplica].

Note: If your database is using a rowversion (or timestamp) column then finding the changed rows will be simpler.

This is not a perfect process and will doubtless miss some details.  But it would give you an approach to help you patch up the damage.

Having said that: It is up to you to decide whether this process is worth the effort.


How to optimise MySQL query that uses dependent subquery I have a web app that has a custom feed of images. The image queried are based on whether the users have previously seen the image and ordered by a Hacker News style score.

There are only 1000 rows in the image table and about 40,000 rows in the seen table.

The current query is taking 7 seconds or more on a small EC2 LEMP stack instance.

SELECT
    images.id AS image_id,
    images.created_at AS time_ago,
    pictures.name AS picture_name,
    pictures.location AS picture_location,
    images.like_count AS like_count,
    images.seen_count AS seen_count,
    images.like_count /
    POWER(
        (HOUR(TIMEDIFF(NOW(), images.created_at))+2), 0.5
    ) AS s
FROM
    images
LEFT JOIN
    pictures ON images.picture_id = pictures.id
WHERE
    images.id NOT IN (SELECT
        image_id
    FROM
        seen
    WHERE
        user_id = $user_id)
AND images.id NOT IN ('$exclude_images_string')
AND images.user_id != $user_id
AND images.like_count >= 10
AND images.moderated IS TRUE
ORDER BY s DESC
LIMIT 30

How could this be optimised to run faster?

Edit: Here are the create table statements for images and seen:

    CREATE TABLE images (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  picture_id int(11) NOT NULL,
  user_id int(11) NOT NULL,
  moderated tinyint(1) DEFAULT NULL,
  seen_count int(11) NOT NULL DEFAULT '0',
  like_count int(11) NOT NULL DEFAULT '0',
  created_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  updated_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (id,picture_id,user_id),
  KEY USER (user_id)
) ENGINE=InnoDB AUTO_INCREMENT=1246 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE seen (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  image_id int(11) NOT NULL,
  user_id int(11) NOT NULL,
  created_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  updated_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (id,image_id,user_id)
) ENGINE=InnoDB AUTO_INCREMENT=39107 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Instead of using a sub query you could use a LEFT JOIN.

That way you negate the need for a sub query which is being looked at for each image.

LEFT JOIN
        seen
    ON
        seen.image_id = images.id
    AND
        seen.user_id = $user_id

Then only get rows that haven't been joined.

WHERE
   seen.id IS NULL


Mongo exception: connect failed on reboot I have Mongo installed on Ubuntu 16.04 machine, on VMWare. I start it using "mongo" command, last week there was no problem. But recently, it gives this error:

MongoDB shell version: 3.2.15
connecting to: test
2017-08-09T09:31:33.634+0300 W NETWORK  [thread1] Failed to connect to 127.0.0.1:27017, in(checking socket for error after poll), reason: errno:111 Connection refused
2017-08-09T09:31:33.712+0300 E QUERY    [thread1] Error: couldn't connect to server 127.0.0.1:27017, connection attempt failed :
connect@src/mongo/shell/mongo.js:229:14
@(connect):1:6

exception: connect failed

I know how to solve it, it is here: https://stackoverflow.com/questions/12831939/couldnt-connect-to-server-127-0-0-127017/34835813#34835813

But everytime I reboot Ubuntu machine, the problem repeats. Why, and how can I prevent this happen everytime?
Thanks.

Edit: Problem solved. You can see the solution below, in my comments to JJussi's answer.

mongod is actual mongodb daemon what "is" the database. mongo is just client program what connects to mongod process (in this case; with sharded cluster it connects to mongos -process).

So, mongod process must be running before mongo can connect it.

You wrote that sudo systemctl enable mongod.service don't work anymore? Check if that service has (some how) changed its name. Just give command sudo systemctl|grep -i mongo and you can see if it is there and it is enabled. IF still your mongod process don't start automatically after reboot, check /var/log/mongodb/mongodb.log (you can check that lock file name with sudo grep path /etc/mongod.conf -command) says. What is the error? Fix that error (maybe chown of directory and files?) and try again with sudo systemctl restart mongod.service (or what ever is services name)


SQL Server logins and users -- assign multiple logins to a single user? In SSMS, since I can't assign multiple login objects to a single user object, what is a best practice to achieve an equivalent security model?

For example, I have 20 logins and want to give them all the same permissions on a single database:

Do I have to create 20 user objects in that database?
Do I have to touch all 20 Login objects?

Since you are using SQL Server local logins, they cannot be grouped.  Each login is independent and each login needs a user in the database.

Where you can get some saving in specifying rights is to create roles, to which you grant, revoke, or deny rights.  (Note that deny rights override grant rights.)  Then use these roles to give rights to the users.

This way a role contains the specifications of permissions, rather than applying those to every user individually.

After this you can add the users to the roles that user needs.  For example:

Allen - ReadOnly role
Sally - ReadOnly role, MaintainAccounts role
Zenubia - UpdateNonSecureData Role
etc.

This preserves your security settings at the role level, so you should not need to repeat those grants.  Just add or remove a user from a role, when needed.


Database schema for passing requests to role based users I am working on a Database schema where the scenario should be:

There are role based Users; Role 1, Role 2, Role 3 and Role 4
There is a Request model (or class, or table)
User with role Role 1 will create a new Request
This Request needs to be executed by a single User from each Role. Execution means passing on a Request from one User to an other and entering comments.
Role 1 will decide which User from each Role will execute the Request
The workflow of execution will be predefined, for now suppose Role 1 > Role 2 > Role 3 > Role 4 and then again > Role 1. The workflow will be cyclic.
But a User associated with a particular Role to execute a Request may change in an other cycle
I need to track the history with who executed a Request with what comments
Workflow should be changeable

I don't know whether I am clearly able to explain my requirement or not. But will request you to try to understand and/or discuss this question with me and help creating me this schema.

The current Db schema which I am thinking of, but is not very useful and scalable:

Request table

- id
- name
- some_other_stuff
- currently_assigned_to

Assignment table

- id
- request_id
- role_1
- role_2
- role_3
- role_4

Workflow table

- id
- request_id
- executed_by
- comment

The Assignment table will handle which User has to execute the Request based on which Role they are assigned to. Role 1 User will execute and the Request will be passed to Role 2 User and further on.

The Workflow table will save the history.

Your help is really appreciated.

OK - here's my first pass at answering your question.

If a user has role1 in a given assigment and can have another role in a different assigment, then my schema will have to change. If necessary, this can be looked at. I think that some of your requirements will require more than SQL to fulfill - you will probably have to start looking at triggers.

You haven't mentioned your RDBMS - my schema is for PostgreSQL. For example MySQL doesn't have the capabilities of PostgreSQL, but you can implement many things  triggers and stored procedures. I would strongly recommend PostgreSQL over MySQL if you're going for an Open Source database. Firebird is also a far more capable RDBMS than MySQL. CHECK CONSTRAINTs, REGEXes, CTEs and analytic functions - not implemented (or very poorly so - REGEXes) in MySQL, present in the others.

There are a couple of extra tables in my schema - let me know if this corresponds to your needs. I'm assuming that a request will be broken into assigments - no need to have 4 roles per assignment - the information on who performed each assignment will be in the workflow tables

You could eliminate the workflow table by putting a user_id there and a boolean for completed - maybe simpler? Though having a small assignment table and moving completed assignments to the workflow table might have  favourable performance impact.

DROP TABLE IF EXISTS workflow;
DROP TABLE IF EXISTS assignment;
DROP TABLE IF EXISTS request;
DROP TABLE IF EXISTS my_user;
DROP TABLE IF EXISTS role;

Remove the tables if they exist.

CREATE TABLE role  -- caution, may be a reserved word in some RDBMSs.
(
  role_id SERIAL CONSTRAINT role_pk PRIMARY KEY,
  role_name VARCHAR(10) NOT NULL,
  role_description VARCHAR(100) NOT NULL
);

CREATE TABLE my_user  -- user is a reserved word in PostgreSQL.
(
  user_id SERIAL  CONSTRAINT user_pk PRIMARY KEY NOT NULL,
  user_name VARCHAR(25) CONSTRAINT user_name_nn NOT NULL,
  user_role INT,                   -- NOT NULL not necessary due to FK constraint
  CONSTRAINT user_role_fk FOREIGN KEY (user_role)
      REFERENCES role (role_id) ON UPDATE CASCADE
);

CREATE TABLE request
(
  request_id SERIAL CONSTRAINT request_pk PRIMARY KEY,
  request_name VARCHAR(20) CONSTRAINT rq_name_nn NOT NULL,
  request_user_id INT CONSTRAINT rq_user_nn NOT NULL,
  CONSTRAINT req_user_fk FOREIGN KEY (request_user_id)
      REFERENCES my_user (user_id) ON UPDATE CASCADE
);

CREATE TABLE assignment
(
  assignment_id SERIAL CONSTRAINT assignment_pk PRIMARY KEY,
  assignment_req_id INT CONSTRAINT assgt_req_id_nn NOT NULL,
  assignment_role INT CONSTRAINT assgt_role_id_nn NOT NULL,
  CONSTRAINT asst_req_fk FOREIGN KEY (assignment_req_id)
      REFERENCES request (request_id) ON UPDATE CASCADE
);

CREATE TABLE workflow
(
  workflow_id SERIAL CONSTRAINT workflow_id PRIMARY KEY,
  workflow_req_id INT CONSTRAINT wkflw_req_id_nn NOT NULL,
  workflow_user_id INT CONSTRAINT wkflw_user_id_nn NOT NULL,
  CONSTRAINT wkflw_req_fk FOREIGN KEY (workflow_req_id)
     REFERENCES request (request_id) ON UPDATE CASCADE,
  CONSTRAINT wkflw_user_fk FOREIGN KEY (workflow_user_id)
     REFERENCES my_user (user_id) ON UPDATE CASCADE
);


Use sqlldr control file to automatically create several tables in Oracle I have a client who has a requirement that I load data from csv files into an Oracle database. Part of the requirement is that I use sqlldr and provide a control file to facilitate loading this data. However there are a lot of text files with a lot of fields, and it will be a lot of labor to create the tables in the database and then create the control file for each table.

I know there are tools which will read a bunch of text files and generate the SQL that is required to create all the tables and load them with data. But that does not involve sqlldr with control file, which is the requirement. Is there a way to do this easily?

The Utilities manual that describes SQL*Loader syntax (which no sane person can recall anyway!) has a number of case studies (doc link here). Case study 5 covers loading into multiple tables. The actual case studies are installed into $ORACLE_HOME/rdbms/demo if you choose to install the companion CD. I seem to recall that in earlier versions they were also described in full in the Utilities manual.


Unable to conditionally commit/rollback transaction I am trying to create a reuable SQL script that I can run that will guard against unintentional db changes based on the expected number of affected rows (not looking to create a sproc/func at this time.)

Using the answer to this SO question, I tried implementing the same but am not having much luck.

I have...

/*create table test (
    val int
);
insert into test (val) values (1);
insert into test (val) values (2);
insert into test (val) values (3);
insert into test (val) values (4); */

SET SQL_SAFE_UPDATES = 0;
set @@autocommit = 0; /* troubleshooting, doesnt seem to affect outcome */
start transaction;

set @expected = 2;  /* should cause rollback */

delete from test where val > 3;

set @rc = Found_rows();
select @rc as row_count;

set @tran = (select if(@rc = @expected,'ROLLBACK','COMMIT'));
prepare stmt from @tran;
execute stmt;

select * from test;

SET SQL_SAFE_UPDATES = 1;

But it appears to be committing the transaction no matter what.

I don't imagine it matters, but this is on an AWS RDS (10.0.24-MariaDB) via MySQL Workbench 6.3

I'm more familiar with MSSQL (if that helps any.)

PS: Also found this suggestion (which I am less certain about.)

There are two errors in your script.

First, delete does not affect "Found_rows()" if you don't have a "returning".  You should use

set @rc = row_count();

Second, your logic in the if(cond,trueval,falseval) is inversed, you should use

set @tran = (select if(@rc = @expected,'COMMIT','ROLLBACK'));

You want to commit when the result equals what you expected, don't you?


FileStream files won't deleted after records deleteion I am working in sql server 2016 with Filestream attribute column.
I have a column with filestream attribute activated on it. when I insert a reacord in TSQL , the associated file is created in Filestream container folder which is correct. but the problem is that when I delete a row , the assoicated file in filestream container remains and won't be deleted. as far as I know this shouldn't happen and the file is completely related to its record, so why doesn't it delete?

Thank you

Unneeded filestream container files are physically deleted by a separate background garbage collection process. The files will eventually be deleted to reclaim the storage, just not in real time.

You can force garbage collection to occur immediately using sp_filestream_force_garbage_collection.


SQL Server bulk delete I had to delete around 600K records daily from a table. The problem I am facing is that whenever delete happens, the log database size increases to 1 GB. Even after the delete operation has been done, log database size doesn't shrink to its normal size. This is a problem as log DB size is increasing to 1 GB every day.

I cannot use truncate as table to be deleted is being referenced by other tables.

When you perform a transaction - like deleting 600K records all at once - SQL Server has to log what it's doing in the transaction log file as it works.

Say the process takes 5 minutes, and 4 minutes into it, the server reboots for some reason. SQL Server starts up, opens the log file, and then needs to undo all that work (because the transaction never committed.

To avoid having a log file that large, you can:

Do your work in smaller transactions - like working in batches
Put your database in simple recovery model (so that transaction log space is available for reuse as soon as your transaction is committed), or
If you're in full recovery model, back up your transaction log frequently, maybe even every minute - because the space is only marked as available for reuse after it's been backed up


Postgresql INSERT triggers "0 row affected" workaround In a INSERT trigger based table partition, insert always give "0 row affected". I think this problem is well know, it is even in the todo.

Is there any workarounds for this issue in 9.4?

You can try to catch the number of inserted rows using GET DIAGNOSTICS and then raise a notice/warning/whatever you prefer.  Here is a small illustration:

DO $$
    DECLARE cnt integer;
    BEGIN
        INSERT INTO b SELECT i FROM generate_series(1,9) t(i);
        GET DIAGNOSTICS cnt := ROW_COUNT;
        RAISE WARNING '% row(s) affected', cnt;
    END;
$$;
-- gives:

WARNING:  9 row(s) affected
DO


MySQL Table Crashed Needs Repair, but Doesn't seem to Repair? I am getting the error:

Table 'feed_data' is marked as crashed and should be repaired

So I run REPAIR TABLE feed_data (repeatedly) and get the following result:

+----------------+--------+----------+---------------------------------------------------------------+
| Table          | Op     | Msg_type | Msg_text                                                      |
+----------------+--------+----------+---------------------------------------------------------------+
| mydb.feed_data | repair | Error    | Table 'feed_data' is marked as crashed and should be repaired |
| mydb.feed_data | repair | status   | Table is already up to date                                   |
+----------------+--------+----------+---------------------------------------------------------------+
2 rows in set (0.06 sec)

The table still shows as crashed.
Why won't the table repair when I run the repair table command? What else can I do to repair the crashed table?


Need to make sense of FileTable permissions I'm setting up a FileTable to allow users to quickly stuff some PDF report files into a database for later searching via an intranet application (to be built later). Sorting out the necessary permissions is a bit of a challenge, and I'm hitting a few roadblocks.

First, I have confirmed that I (with sysadmin privileges) can add and remove files to the share without any issues, so the FileTable is functional at least.

I've added an end user to an AD group, created a Windows login in SQL Server for this group, mapped it to a database user, and granted that user select, insert, update, and delete permissions on the FileTable. The user has logged out of his computer and logged in again to make sure the group I added him to is in the auth token.

He can access the base filestream share for the instance (\\servername\FileStream), but when he tries to go deeper to the database-specific directory (\\servername\FileStream\DatabaseFileStreamDirectoryName), he gets a permission error ("You do not have permission to access..."). Same thing if he tries to access the FileTable path directly (\\servername\FileStream\DatabaseFileStreamDirectoryName\PDFReports).

So, what is the proper way to grant access so that users can browse to the share subdirectory for a specific database, see the directories in there for FileTables, and ultimately browse/access files within the FileTables? It's apparently more than just a user mapping in the database, and select/insert/update/delete permissions on the table, and I'm not finding a whole lot of clear documentation on this so far.

Here's the (slightly anonymized) code I've used for setting this up:

USE master
GO
ALTER DATABASE AppData ADD FILEGROUP FS CONTAINS FILESTREAM
ALTER DATABASE AppData ADD FILE ( NAME = N'FS', FILENAME = N'S:\Filestream\AppData\FS' ) TO FILEGROUP [FS]
ALTER DATABASE AppData SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE AppData SET FILESTREAM( DIRECTORY_NAME = N'AppData' ) WITH NO_WAIT
ALTER DATABASE AppData SET MULTI_USER
GO
USE AppData
GO
CREATE TABLE PDFReports AS FileTable WITH (FileTable_Directory = 'PDFReports')

GRANT SELECT, INSERT, UPDATE, DELETE ON PDFReports TO [MyDomain\App Operators]

I've had to resort to a bit of guesswork, but as far as I can tell, I haven't done anything overly permissive.

I started with a non-privileged test account that's a member of the AD group I created, and I was able to navigate as far as the FileStream directory for the database (\\servername\FileStream\DatabaseFileStreamDirectoryName), but nothing was visible in there.

So, I tried the most obvious approach first:

GRANT VIEW DEFINITION ON PDFReports TO [MyDomain\App Operators]

...Then hit F5 in the VM I was browsing from, and the directory for PDF reports showed right up. I was able to browse in there, and copy some files to the directory.

So I think I'm good, but I'd appreciate anybody piping up if I've done anything bone-headed.


How to ensure backups are successfully persisted to S3 when using Ola Hallengren solution on AWS EC2 Instances Improving Existing Backup Approach By Using Ola Hallengren Backup Solution
Deploying a new backup approach to eliminate dependency on a vendor tool (Cloudberry) that I've leveraged before. In my microblog post, I describe the process I'm taking which includes:

Creating and deploying all the maintenance solution via dbatools
Leveraging s5cmd as a sync tool to ensure backups get copied from EBS volume to S3. (performant alternative to installing aws-cli).
Using Red Gate SQL Monitor/PagerDuty integrations for monitoring and alerting on issues.

Painpoint - Replacing Existing Tool
The question is marked for AWS EC2 specifically as much of this is solved in Azure with the ability to backup to Azure Blob storage supported by SQL Server.
Cloudberry is a great budget oriented tool that allows you to use a GUI to schedule backups that automatically get pushed to S3. It's a pretty good budget solution, but I've seen serious scaling issues once you deal with thousands of databases. At this point, the local sqlite database and S3 metadata queries seem to cause slow-downs and impact the server RPO.
I've evaluated other tooling, but very few of them were flexible with S3 uploads as a native feature.
An eye towards reliability
As I've worked through the final steps I'm looking for points for failure and reliability concerns.
One area that becomes a bit less easy to validate is the successful sync of all contents to S3.
While the agent should fail with an exit code, knowing all files did get up to S3 is important.
How to Gain Assurance of All Files Persisted To S3
Here's a few questions specifically that I'm trying to work through and would appreciate any insight on.

Can EBS Snapshots replace the need to sync to s3 with a goal of 15 min RPO on a dedicated backup volume? I'm not including an data files/log files in this, so it would purely a backup drive.

Based on some prior tests I believe a shorter interval is difficult to get on EBS.

S5cmd performs very well, but isn't as controlled as doing this with a dedicated PowerShell script. For instance, simply iterating through the S3 files to perhaps generate a diff report at the end would take 8 seconds along on s5cmd and 43 seconds getting it via AWS PowerShell tools. With this running every 15 minutes I want as quick and minimal of a performance impact as possible to the server, not run a lot of custom scripts beyond this.
Is there any approach you'd take to audit the local directory of backups against S3 can validate nothing locally is missing or is there where relying on the sync tool just has to be done.
Any usage of AWS Backup, Data Sync, or other tooling natively integrated in AWS that could solve these issues?  FSx, DataSync, and others seem to add more risk and complexity to manage.

Other Solutions

I've considered dbatools based backups in the past, as I could gain full control of running the backup, pushing to s3, and logging all of this with more control. However, after deliberation and community discussion I decided against this as I felt the use-case for dbatools was more for adhoc backups and that leveraging Ola Hallengren Backup tooling was a more robust solution for production usage. The negative to using it is that my PowerShell error handling and logging isn't going to be implemented.

I look forward to any insight and appreciate the help

Have you tried using the AWSCLI? I have an Agent Job that runs a .bat after my backups are complete. The bat runs the sync command. I don't delete anything from S3, I simply let the CLI sync new or changed files up to my bucket. I then use bucket policies to manage the lifetime of my backups that are up there.
My bat script is below. You will need to provide your own paths. Don't forget to run AWS Configure to setup the CLI. I suggest creating a specific profile that has least privilege access to upload to S3. This is for Windows.
setlocal

set  tm=%time: =0%
set  tm=%tm:~0,2%_%tm:~3,2%_%tm:~6,2%
set dt=%date:~10,4%_%date:~4,2%_%date:~7,2%

echo "Starting..." >> "L:\Logs\SyncBackups\s3SyncBak_%dt%_%tm%.txt"

aws s3 sync "B:\Database" "s3://MyBucketName/ProductionDatabaseBackups" --exclude "*" --include "*.bak" --profile MyProfileName >> "L:\Logs\SyncBackups\s3SyncBak_%dt%_%tm%.txt"

echo "Synced with no problem ..." >> "L:\Logs\SyncBackups\s3SyncBak_%dt%_%tm%.txt"


Oracle: Delete row in a table using triggers while deleting value in another table So, I basically have those 3 tables:

tbl_albums ----> (id, name)

tbl_album_has_songs ----> (id_album, id_song)

tbl_songs -----> (id, name)

I need to write a trigger that deletes a row from table tbl_album when there are no more songs associated with that album on tbl_album_has_songs.

To do this, I need to count how many times a certain tbl_albums.id = tbl_album_has_songs.id_album appears on tbl_album_has_songs. If count is 0, then I delete the album from tbl_albums.

I've made the following trigger:

CREATE OR REPLACE TRIGGER delete_album after update or delete on tbl_album_has_songs
REFERENCING OLD AS old NEW AS new
FOR EACH ROW
declare lv NUMBER;
begin
select count(*) into lv from tbl_album_has_songs where id_album =:old.id_album;
if lv = 0 then
delete from tbl_album where album.id = :old.id_album;
end if;
commit;
end;

but when I tried to delete some songs from tbl_album_has_songs it throws the following error:

Error starting at line 1 in command:
INSERT INTO tbl_equiporder VALUES (1,101,'11-sep-13',1000,1)
Error report:
SQL Error: ORA-04091: table S24585181.TBL_EQUIPORDER is mutating, trigger/function may not see it
ORA-06512: at "S24585181.TRIGGER_EQUIPORDER", line 9
ORA-04088: error during execution of trigger 'S24585181.TRIGGER_EQUIPORDER'
04091. 00000 -  "table %s.%s is mutating, trigger/function may not see it"
*Cause:    A trigger (or a user defined plsql function that is referenced in
           this statement) attempted to look at (or modify) a table that was
           in the middle of being modified by the statement which fired it.
*Action:   Rewrite the trigger (or function) so it does not read that table.

Any help?

You can't select or modify the underlying table of the trigger during trigger processing.  In this case, you are selecting.

Don't perform this work in a trigger.  Manage the delete in PL/SQL application code where you can perform the delete, count for the children, and then delete the parent when there are no children.  Basically what I'm interpreting your logic to be.


oracle oraenv script bash I want to do same commande shell CLi into a script

Command shell without script (CLI)

su - oracle
export ORAENV_ASK=NO
export ORACLE_SID=HRPRD
. oraenv
env | grep ora

output:

USER=oracle
**LD_LIBRARY_PATH**=/u01/pe/oracle/u/25/bas/lib
ORACLE_BASE=/u01/pe/oracle
MAIL=/var/spool/mail/oracle
PATH=/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/u01/pe/oracle/u/25/bas
PWD=/oracle
HOME=/oracle
LOGNAME=oracle
**ORACLE_HOME**=/u01/pe/oracle/u/25/bas/

My script

rr=`su - oracle`
export ORAENV_ASK=NO
export ORACLE_SID=MySID
uu=`. oraenv`
searchOraHome= `env | grep ora`
echo $searchOraHome

output:

USER=oracle ORACLE_BASE=/u01/app/oracle
PATH=/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/oracle/bin
MAIL=/var/spool/mail/oracle PWD=/oracle HOME=/oracle LOGNAME=oracle

Why are the variables ORACLE_HOME and LD_LIBRARY_PATH not present with the script shell?

Is the line:

uu=`. oraenv`

... not correct?

If you us a shell to execute a command in backticks (`), it is executed in a subshell, so all the environment variables it sets are only set in the subshell and are gone when the subshell exits.

You have to "source" the script with

. oraenv

without the backticks.


Relative path for psql \copy file? I am attempting to create a collection of scripts to modify some tables in a Postgres 9.6 database. These scripts are stored in a variety of directories, and are in turn called from one master coordinator script. I'm running the scripts in psql.

In the master script, I use \ir to specify relative pathnames to the other scripts. Accordingly, I can run psql from anywhere, and the master script will find the other scripts. This works as expected.

In some of the scripts, however, I am using the \copy command to load data from CSV files. Unfortunately, it appears that the input filename for \copy is always interpreted as relative to psql's current working directory, rather than relative to its script's directory, even if I call the script containing the \copy command using \ir.

For example, let's say I start psql in C:\, and plan to use the following files:

C:\
    dir1\
        script1.sql
        dir2\
            script2.sql
            input.csv

I can call the first script using \ir dir1/script1.sql. That works fine.

Also, that script can call the second script using \ir dir2/script2.sql. That works fine, as well.

The problem is the \copy command in the second script:

\COPY foo.bar (col1) FROM 'input.csv' WITH (FORMAT CSV);

This fails unless input.csv exists in the current directory of the psql session. Therefore, my question is:

Is there any way for me to call the \COPY ... FROM command from inside a script without requiring the user to run the psql session from a specific directory relative to the input file?

I understand that I could use an absolute path name, but we're going to be running this script from a variety of different machines/platforms when upgrading our different deployments, so there is no common absolute path that will work in this case. I'm looking for some sort of relative or configurable path solution.

Thank you, in advance.

I think there's no way to indicate to \copy that the file is relative to the location of the script, but you may use \cd to change the current directory before \copy.

\cd interpolates variables so the directory can be passed on the command line with -v:

psql -vscriptdir="c:\path\to\script" -f c:\path\to\script\script1.sql

Before including anything, say there's this command in the top-level script:

\cd :scriptdir

(except if the -f argument is simply a file without a path:  in this case it's not necessary since the current directory is already set where the script is)

Then every inclusion of a script in a subdirectory should be:

\cd dir2
\i script2.sql
\cd ..

(as opposed to \ir dir2/script2.sql)

When script2.sql will invoke \copy with a file relative to it, the data file will be found since it's in the current directory.

That should work recursively, assuming scripts in subdirectories also follow that convention, and that they don't do an unrelated \cd that would interfere with the \cd .. waiting to happen on the way back to the top.


Why CTX_DDL.SYNC_INDEX procedure doesn't work? I'm following the Oracle documentation to create indexes with Oracle Text. The problem is, when I update my data and want to synchronize my index with the CTX_DDL.SYNC_INDEX procedure (according to the step 4 of the documentation), there is no change.

I don't post my code because it is exactly the same as the documentation.

An idea about the source of this behavior?

To perform this operation, the user who executes the SYNC_INDEX procedure has to be the owner of the index. So, in my case, I had to give the execution rights on the package with the CTXSYS user:
GRANT EXECUTE ON CTXSYS.CTX_DDL TO myuser;

Now, I can synchronize my index with myuser:
EXEC CTX_DDL.SYNC_INDEX('idx_docs', '2M');
SELECT SCORE(1), id, text FROM docs WHERE CONTAINS(text, 'city', 1) > 0;

I got the same result as the documentation:
  SCORE(1)         ID TEXT
---------- ---------- -------------------------------------------------
         4          2 Paris is a city in France.
         4          4 Los Angeles is a city in California.
         4          5 Mexico City is big.


information_schema is full of NULLs after database import I am using MySQL workbench to dump 4 databases with tables that reference each other. As a note, there are no current problems with the databases at all - everything works as expected. When I try various data exports from MySQL workbench and then import it to another PC with MySQL workbench, everything appears to be fine except that queries to information_schema are returning results full of NULL
On my working setup, if I run this query
SELECT k.COLUMN_NAME, k.REFERENCED_TABLE_SCHEMA, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME
        FROM information_schema.TABLE_CONSTRAINTS i
        LEFT JOIN information_schema.KEY_COLUMN_USAGE k ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME
        WHERE i.CONSTRAINT_TYPE = 'FOREIGN KEY'
        AND i.TABLE_SCHEMA = 'details'
        AND i.TABLE_NAME = 'locales'

I will get a whole bunch of information about fkey relationships.
After I dump and import these databases to another PC, and run this query again, I get the same amount of rows but all data is NULL. When I investigate the tables in MySQL workbench, they all show proper foreign keys! But I need the above query to produce correct results because I use it in my PHP application to display foreign keys to the user. What's worse - if I make two new tables and set up fkeys between them, information_schema still returns more rows full of NULL values using the same query as above. It's like the whole thing is broken for the database.
How can I get the above query to not return rows full of NULLs? What could be wrong?

mysql doesn't find a LEFT JOIN with both tables.
You can use:
SELECT kcu.*,tc.* FROM information_schema.KEY_COLUMN_USAGE kcu
LEFT JOIN  information_schema.TABLE_CONSTRAINTS tc ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
WHERE tc.CONSTRAINT_TYPE = 'FOREIGN KEY'
AND tc.TABLE_SCHEMA = 'testdb';

SELECT kcu.*,tc.* FROM information_schema.TABLE_CONSTRAINTS tc
RIGHT JOIN information_schema.KEY_COLUMN_USAGE kcu ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
WHERE tc.CONSTRAINT_TYPE = 'FOREIGN KEY'
AND tc.TABLE_SCHEMA = 'testdb'

I think it is a bug, which you can report, or else tell me and i report it


ER Schema - bank system Hello i'm trying to model a simple banking scheme
where I have 3 transactions: withdraw, transfer, deposit.
of these 3 operations, the transfer is the only one that needs more attributes, I need to know the accounts participating in the transfer, and who received and who sent it.
At first I thought of something simple like that, but it seems very strange:

but this seems very vague to me, and when do I need the statement from the two accounts participating in the transfer?
could someone help me with this?

The way I would initially think of this (as an accountant in a previous life) is not as a transfer, but as what is going on in MY bank's account? One stored procedure would deduct funds from one of my member's accounts, and a second stored procedure (initiated by the application) would send something to the other bank system to deposit funds. The record of the transfer would go into the individual account's records as just another transaction, like a deposit or withdrawal. There would be no lasting connection between the two accounts.
Even if the transfer was between a member's checking and savings accounts, they would still be treated as essentially two transactions going on at nearly the same time. One deposit, one withdrawal.


Cannot drop database in postgres I would like to unserstand why I am not able to drop a database from cmd:
I execute the command: drop database if exists <db_name> but the result is as below:
postgres=# drop database testV5;

ERROR:  database "testV5" does not exist

knowing that the database exists:
postgres=# \l
                                  Liste des bases de donnÚes

   Nom     | PropriÚtaire | Encodage |  Collationnement   |    Type caract.    |    Droits d'accÞs
  testV4   | postgres     | UTF8     | French_Canada.1252 | French_Canada.1252 |
  testV5   | postgres     | UTF8     | French_Canada.1252 | French_Canada.1252 |
  postgres | postgres     | UTF8     | French_Canada.1252 | French_Canada.1252 |
  template0| postgres     | UTF8     | French_Canada.1252 | French_Canada.1252 |
  template1| postgres     | UTF8     | French_Canada.1252 | French_Canada.1252 |

Can anyone explain that please ?
Thanks in advance.

You created the database with double quotes, so the name is case sensitive now. You need to use the dreaded double quotes when dropping the database as well:
drop database "testV5";

Note that using quoted identifiers is discouraged


Is it possible to mix mongod version in a sharded cluster I'm playing around with sharded cluster and I'm thought I saw somewhere that different mongo version could live within the same cluster.

For example :

mongos : 4.0
configRs : 4.0
shardRs01 : 3.4
shardRs02 : 4.0

It would have saved me a lot of time because I would have migrated my shardRs01 to 4.0 (and debian 9) by simply moving the chunks to the rest of the cluster and deleting it once done.

But after thinking about it, it seemed strange that it could be possible, due to the compatibility issue between mongo versions.

And my tests are supporting this conclusion :

I cannot add a 3.4 shard into a 4.0 cluster
Reversely I cannot add a 4.0 shard into a 3.6 cluster

So I wanted to make sure that it was the case that It is not possible to add a shard that does not use the same mongo version as the cluster.

It is possible to mix versions from adjacent major MongoDB server releases (for example: 3.6 and 4.0) for the purposes of upgrading, but in general a cluster should have consistent versions. Successive server versions are expected to add new features (additional data types, authentication mechanisms, protocol improvements, new commands, ...) that do not necessarily interoperate with every prior release. Upgrade steps and compatibility details are included in the release notes in the MongoDB documentation.

As part of the upgrade process for MongoDB 3.4+ there is now an intentional step of setting a Feature Compatibility Version (FCV) to toggle features that persist incompatible data changes while an upgrade is in progress. If you started with a MongoDB 4.0 deployment (rather than upgrading from a prior version), new 4.0 features will be enabled by default.

I highly recommend matching your mongo shell and command line tools with the version of your MongoDB server, and checking the compatibility for your driver.

  I cannot add a 3.4 shard into a 4.0 cluster

Correct. If the 3.4 deployment is currently an independent replica set, it would have to be upgraded to MongoDB 3.6 and then upgraded to MongoDB 4.0 before adding to a 4.0 sharded cluster.

If you were starting with a 3.4 sharded cluster, all components of the cluster would have to be upgraded to MongoDB 3.6 (including setting FCV to 3.6) before continuing with the upgrade to MongoDB 4.0.

  Reversely I cannot add a 4.0 shard into a 3.6 cluster

In theory you could downgrade a 4.0 replica set if you want to add it to a 3.6 cluster, but this is more challenging than upgrading. Any backwards incompatible data that has been persisted would have to be removed or updated.


BIG Binary column - PostgreSQL 9.2 I've got the following table:

CREATE TABLE gorfs.inode_segments
(
  st_ino "gorfs"."ino_t" NOT NULL, -- Inode number the segment belongs to. alongside segment_index, it forms the table's primary key to ensure uniqueness per relevant scope
  segment_index "gorfs"."pathname_component" NOT NULL, -- See st_no's column description for further details. The meaning of this column varies based on the host inode type:...
  st_ino_target "gorfs"."ino_t", -- Target inode number. Meaningful for directory inode segments (objects in the directory)
  full_path "gorfs"."absolute_pathname", -- Exploded absolute canonical path for quick lookups. Meaningful only for directory inode segments (objects in the directory)
  segment_data "bytea", -- Actual data segment. Meaningful only for S_IFLNK and S_IFREG....
  CONSTRAINT pk_inode_segments PRIMARY KEY ("st_ino", "segment_index"),
  CONSTRAINT fk_host_inode_must_exist FOREIGN KEY (st_ino)
      REFERENCES gorfs.inodes (st_ino) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_target_inode_must_exist FOREIGN KEY (st_ino_target)
      REFERENCES gorfs.inodes (st_ino) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT uc_no_duplicate_full_paths UNIQUE ("full_path"),
  CONSTRAINT cc_only_root_can_be_its_own_parent CHECK ("st_ino_target" IS NULL OR "st_ino"::bigint <> "st_ino_target"::bigint OR "st_ino"::bigint = 2)
)

The column "segment_data" has all the attachments from the customers, like photos, documents, audio, etc.

To improve the performance, we'll be migrating all the data from the segment_data column to a NFS server.

Full database size: 2 TB
Using PostgreSQL 9.2
Table gorfs.inode_segments size: 1698 GB (Command: SELECT
pg_size_pretty( pg_total_relation_size('gorfs.inode_segments'));)
Basically we'll be using: UPDATE gorfs.inode_segments SET segment_data = NULL WHERE batch_number = 0
We won't be migrating all the data on the segment_data column by once, it will be done gradually (over few weeks).

Question:

Just by setting "NULL" into that column, will I get free space on the database? Will I have to run any command such VACUUM/VACUUM FULL/ANALYZE/PG_DUMP?

PostgreSQL's MVCC is used over-writing mechanism,  which is roughly mentioned to this document , so all updated rows are inserted into the table. Thus, you cannot get free space.  On the contrary, the table size is temporarily increased.

(Even though autovacuum feature works  very efficiently,) I think you have to do VACUUM FULL in order to certainly remove dead rows(tuples) and dead pages after updating. That way (do UPDATE SET NULL and VACUUM FULL), the size of the table will be small.


Problems in opening MySQL Query Browser My MySQL Query Browser doesn't run. I just started to work with XAMPP and PHP.
I just installed XAMPP on my local machine. Now I have a problem when opening MySQL Query Browser. What I'm getting while trying to open it is:

Could not connect to host 'localhost'. MySQL Error Nr. 2002 Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
Click the Ping button to see if there is a networking problem.

Can anyone help me to fix the above problem?

This question is already being answered https://stackoverflow.com/questions/5376427/cant-connect-to-local-mysql-server-through-socket-var-mysql-mysql-sock-38:

  If your file my.cnf (usually in the /etc/mysql/ folder) is correctly configured with

socket=/var/lib/mysql/mysql.sock

  you can check if mysql is running with the following command:

mysqladmin -u root -p status

  try changing your permission to mysql folder. If you are working locally, you can try:

sudo chmod -R 755 /var/lib/mysql/

  that solved it for me


Cannot create a stored procedure in any db in one instance SQL Server 2012 Simple generic stored procedure not complicated, could return string or just date and all fail.

create procedure get_date
as
select getdate()

Error:

  Msg 3616, Level 16, State 2, Procedure get_date, Line 4
  An error was raised during trigger execution. The batch has been aborted and the user transaction, if any, has been rolled back.

This same error in any db in the instance.

Sounds like you've got a server-level DDL trigger that's failing somehow. Check for it under Server Objects -> Triggers & see if you can see why it's failing.


What is the best way to insert a XML input to an oracle table in stored procedure? I want to write a stored procedure which has a XML input (clob type) and inserts the data to a table. my XML structure is something like this:

<rowset>
    <row>
        <name>tom</name>
        <family>mack</family>
        ....
    </row>
    <row>
        <name>marry</name>
        <family>skot</family>
        ....
    </row>
    ...
</rowset>

I have just stock in many options that oracle gave me to handle this. using extractvalue and XMLSequence- using DBMS_XMLDOM- using DBMS_XMLStore or many other options.

I want to know which one is better according to the performance and resources that use. the input XML is usually less than 50KB.

You can query your XML data directly in SQL with XMLTABLE:

SQL> VARIABLE xml VARCHAR2(4000);
SQL> BEGIN :xml :=
  2  '<rowset>
  3       <row>
  4           <name>tom</name>
  5           <family>mack</family>
  6       </row>
  7       <row>
  8           <name>marry</name>
  9           <family>skot</family>
 10       </row>
 11   </rowset>';
 12  END;
 13  /

PL/SQL procedure successfully completed

SQL> SELECT x.*
  2    FROM XMLTable('for $i in /rowset/row return $i'
  3                    passing XMLTYPE(:xml) columns
  4                       name VARCHAR2(200) path 'name',
  5                       family VARCHAR2(200) path 'family'
  6                  ) x;

NAME    FAMILY
------- -------
tom     mack
marry   skot

You can also use XMLSequence and EXTRACT (see this SO question for example) but as noted in the documentation these functions are deprectated.

Another option is DBMS_XMLSTORE, although you can't really customize it much (needs perfect match between input xml tags and output column names):

SQL> CREATE TABLE my_table ("name" VARCHAR2(10), "family" VARCHAR2(10));

Table created

SQL> DECLARE
  2     l_ctx  dbms_xmlstore.ctxtype;
  3     l_rows NUMBER;
  4  BEGIN
  5     l_ctx := dbms_xmlstore.newcontext('MY_TABLE'); -- Get saved context
  6     dbms_xmlstore.clearUpdateColumnList(l_ctx); -- Clear the update settings
  7
  8     -- Set the columns to be updated/inserted as a list of values
  9     dbms_xmlstore.setUpdateColumn(l_ctx, 'name');
 10     dbms_xmlstore.setUpdateColumn(l_ctx, 'family');
 11     -- Set ROW tag
 12     dbms_xmlstore.setRowTag(l_ctx, 'row');
 13
 14    -- Insert the doc.
 15    l_rows := DBMS_XMLSTORE.insertXML(l_ctx, :xml);
 16    DBMS_OUTPUT.put_line(l_rows || ' rows inserted.');
 17
 18    -- Close the context
 19    DBMS_XMLSTORE.closeContext(l_ctx);
 20
 21  END;
 22  /

2 rows inserted.

PL/SQL procedure successfully completed

SQL> select * from my_table;

name       family
---------- ----------
tom        mack
marry      skot

I'm not sure performance-wise which option is faster, this certainly depends on the data -- you'll have to benchmark.