Error: pq: 1 columns must reference exactly 1 columns in referenced table (found 2) I have a few tables and I am facing this issue most likely with the table "item".
When I remove restaurant_id and category_id, it works fine.

CREATE TABLE IF NOT EXISTS restaurant
(
    id              UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
    title           VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS restaurant_gallery
(
    id              SERIAL,
    restaurant_id   UUID NOT NULL REFERENCES restaurant,
    image           VARCHAR NOT NULL,
    PRIMARY KEY (id, restaurant_id)
);

CREATE TABLE IF NOT EXISTS category
(
    id              SERIAL NOT NULL PRIMARY KEY,
    restaurant      UUID NOT NULL,
    label           VARCHAR(25) NOT NULL,

    UNIQUE (restaurant, label)
);

CREATE TABLE IF NOT EXISTS item
(
    id              SERIAL NOT NULL PRIMARY KEY,
    restaurant_id   UUID NOT NULL REFERENCES restaurant (id),
    category_id     SERIAL NOT NULL REFERENCES category (id),
    name            VARCHAR(25) NOT NULL,
    UNIQUE (id, restaurant_id)
);

CREATE TABLE IF NOT EXISTS item_gallery
(
    id              SERIAL,
    item_id         INT NOT NULL REFERENCES item,
    image           VARCHAR NOT NULL,
    PRIMARY KEY (id, item_id)
);

  Error:

pq: 1 columns must reference exactly 1 columns in referenced table (found 2)
Error: pq: 1 columns must reference exactly 1 columns in referenced table (found 2)
Failed running "sql"

Any help is highly appreciated!

For a foreign key, you need to tell Postgres which table you want to refer to and the name(s) of the field(s) within that table that you want to use as the foreign key(s):

CREATE TABLE IF NOT EXISTS item
(
    id              SERIAL,
    restaurant_id   UUID         NOT NULL  REFERENCES restaurant ( id )
    category_id     INT          NOT NULL  REFERENCES category ( id )
    name            VARCHAR(25)  NOT NULL,
    PRIMARY KEY (id, restaurant_id)
);


Bulk insert inserting 0 rows I am trying to use bulk insert to insert data from a csv file into a sql server table, but it is returning 0 rows.

This is the bulk insert statement I am using

BULK INSERT dbo.DEA_Availability from 'C:\SQLPOWERDOC\CSV\DEA_Availability.csv'
with (
FIRSTROW=2,
FIELDTERMINATOR=',',
ROWTERMINATOR='\r\n',
ERRORFILE = 'C:\SQLPOWERDOC\CSV\DEA_Availability_ERROR.log',
DATAFILETYPE='char',
keepnulls
)

The table definition is:

CREATE TABLE [dbo].[DEA_AVAILABILITY](
    [Server_Name] [varchar](max) NULL,
    [Database_Name] [varchar](max) NULL,
    [Priority] [varchar](max) NULL,
    [Description] [varchar](max) NULL,
    [Details] [varchar](max) NULL,
    [URL] [varchar](max) NULL,
    [IMPORT_DATE] [datetime2](7) NULL
) ON [PRIMARY]
GO

This is the contents of the CSV file

Server Name,Database Name,Priority,Description,Details,URL
HOSTNAME,,None,Cluster Node,This is a node in a cluster.,http://msdn.microsoft.com/en-us/library/ms189134(v=sql.100).aspx

I have verified that each line of the CSV file does end with \r\n

To get this working:

Change the ROWTERMINATOR to '\n'. I'm not sure why '\r\n' isn't
working, but '\n' works with your example data.
Remove KEEPNULLS. NULL values in the CSV for a nullable column will still be brought through as NULL to the database, but this
prevents the NULL for IMPORT_DATE coming through.
Change IMPORT_DATE to have a DEFAULT value of GETDATE() but leave it as nullable (otherwise the import fails)
Add IMPORT_DATE as a column in the CSV
Add a new line after your last data row to ensure the final field is terminated in a way that SQL can detect when importing.

After these changes, the data imports correctly. NOTE: If you want IMPORT_DATE to come through as NULL instead of using the current date, skip step 3.


Is the transfer time from PostgreSQL to the application optimisable? I have a very simple database running on PostgreSQL 9.3. In this database I have one table called "vehicle_states" created as is:

create table MOBILE_STATE_MESSAGE
(
    id_mobile_state_message SERIAL PRIMARY KEY,
    topic_name VARCHAR(100) NOT NULL,
    date_time TIMESTAMP NOT NULL,

    latitude DOUBLE PRECISION NULL,
    longitude DOUBLE PRECISION NULL,
    altitude REAL NULL,
    depth REAL NULL,
    heading REAL NULL,
    roll REAL NULL,
    pitch REAL NULL,
    speed_along_speed REAL NULL,
    speed_along_direction REAL NULL,
    speed_vector_vx REAL NULL,
    speed_vector_vy REAL NULL
);
CREATE INDEX topic_name_idx on mobile_state_message (topic_name)

So this table is the history of some vehicles. Each vehicles has a different name (the topic_name column). Then, I have an index on the topic names.

This database is populated with data. I have 250000 positions for each vehicles, and 5 vehicles. So I have 1000000 rows in the table.

In pgadmin I run the following query :

SELECT
  mobile_state_message.latitude,
  mobile_state_message.longitude
FROM
  public.mobile_state_message
WHERE
  topic_name = 'MyCar';

So, I ask for all the positions of "MyCar" vehicle.
This request took 14 seconds to execute in pgAdmin.

When I start this request in "analyze" mode, with EXPLAIN ANALYZE, I have the following result :

Bitmap Heap Scan on mobile_state_message  (cost=6448.78..26264.75 rows=251917 width=16) (actual time=28.570..79.810 rows=250000 loops=1)
  Recheck Cond: ((topic_name)::text = 'MyCar'::text)
  ->  Bitmap Index Scan on topic_name_idx  (cost=0.00..6385.80 rows=251917 width=0) (actual time=25.752..25.752 rows=250000 loops=1)
        Index Cond: ((topic_name)::text = 'MyCar'::text)
Total runtime: 87.527 ms

Here I see that the request is processed in 87 ms.

I read some posts on the difference between EXPLAIN ANALYZE and Normal request. They said that the main difference is that when we perform an ANALYZE, there is no result data serialization.

My database server and pgAdmin are running on the same computer, so this can't be a network bandwidth problem.

So can I speed up this serialization time ?

Do you think that 14 seconds to fetch 250000 rows containing 2 double values is a normal timing?

Before posting this message I read the Slow Query Question wiki page. I played with some server parameters like shared_buffers, effective_cache_size, and work_mem, but these parameters didn't speed up anything for my particular problem.

That time is spent by pgAdmin to pack and render data and is not the time spent by Postgres to complete the query execution. Why are you fetching 250.000 rows into pgAdmin? If you need to export the table to a plain-text file (like a CSV with header) you can execute this query:

COPY (
  SELECT
    mobile_state_message.latitude,
    mobile_state_message.longitude
  FROM
    public.mobile_state_message
  WHERE
    topic_name = 'MyCar'
)
TO '/path/to/file.csv' WITH (FORMAT CSV, HEADER);

Remember that /path/to/ must be owned by postgres user, so run chown -R postgres /path/to/.

This way you can use wathever to analyze the table (if you need to).


Select post and concatenating all categories it belongs to I have 3 DB tables

POSTS
+----------+----------+----------+
| id       |  title   |  content |
+----------+----------+----------+

CATEGORIES
+----------+----------+
| id       |  title   |
+----------+----------+

POSTS_CATEGORIES
+----------+----------+--------------+
| id       |  post_id |  category_id |
+----------+----------+--------------+

Now lets say I have the following posts:

id=1, title=birds and it belongs to category 1 and 4

id=2, title=apple and it does not belong to any categories

My Query:

SELECT p.id, p.title, p.content
GROUP_CONCAT(DISTINCT p_c.category_id ORDER BY p_c.category_id ASC SEPARATOR ',') AS categories
FROM posts_categories AS p_c, posts as p
WHERE p.id = 1 AND p.id = p_c.post_id
GROUP BY p_c.post_id

This query works for id=1 but when a post does not have any categories, like id=2 it won't find anything. I've tried to modify this a bit, but I always end up with no results or all the fields as null when I'm looking for id=2

I know I could get around this with 2 queries, but was trying to do it with one query. I'm using mysql 5.5.27

This should get all posts and a list of categories (If no categories, then 'None')

SELECT
    p.id, p.title, p.content,
    IFNULL(GROUP_CONCAT(p_c.category_id
    ORDER BY p_c.category_id ASC SEPARATOR ','),'None') category
FROM
    posts p
    LEFT JOIN posts_categories p_c ON p.id = p_c.post_id
    LEFT JOIN categories c ON p_c.category_id = c.id
WHERE
    p.id = 1
GROUP BY
    p.id
;

Give it a Try !!!


Beginner PL/SQL: Return row value from dynamic SQL function I have a PL/SQL function that successfully compiles:

01    WITH FUNCTION find_CV_errors(
02        l_table_name IN VARCHAR
03        ,l_field_name IN VARCHAR
04        ,l_domain_name IN VARCHAR
05        )
06        RETURN VARCHAR
07        AS
08        l_return_value VARCHAR(128);
09    BEGIN
10
11    EXECUTE IMMEDIATE
12        'SELECT
13            LISTAGG(CAST(' || l_field_name || ' AS VARCHAR(50)),  ' ||  ''';  ''' || ') WITHIN GROUP (ORDER BY NULL)
14        FROM ' ||
15            l_table_name ||
16        ' LEFT JOIN
17            (
18            SELECT CODE
19            FROM  ENG.D_CV_ENG_VW
20            WHERE DOMAIN = :bv1
21            )
22        ON ' || l_field_name || '  = code
23        WHERE ' ||
24            l_field_name || ' IS NOT NULL
25            AND code IS NULL'
26    INTO l_return_value
27    USING l_domain_name;
28    RETURN  l_return_value;
29    END;

The function has a dynamic sql query that outputs a single column in a single row via LISTAGG.

I want the function to return the the dynamic sql output as it's return value. However, right now it just returns NULL, because I haven't assigned a value to the l_return_value variable.

How can I assign the output from the dynamic SQL query to the l_return_value variable?

Update #1:

I've added INTO l_return_value; to the query (LINE 26), but now I'm getting an error:

ORA-06553:PLS-103: Encountered the symbol "L_FIELD_NAME" when expecting one of the following:  : = , ( @ % ;

Update #2:

When I remove the semicolon from the end of line 26, the function returns the value of l_field_name, not the value from the dynamic sql query output.

Update #3:

In addition to the errors Balazs Papp pointed out, I had a bind variable on line 13, when really I should have used a concatenated variable. Since Balazs Papp's answer uses generic examples, and not a corrected version of the question, I've corrected the original question myself (lines 07, 13, 26, 27, 28).

For a single row, use EXECUTE IMMEDIATE ... INTO. A simple example:

set serveroutput on
declare
  l_result number;
begin
  execute immediate 'select count(*) from dual' into l_result;
  dbms_output.put_line(l_result);
end;
/

1

For multiple rows, use EXECUTE IMMEDIATE .. BULK COLLECT INTO, here is an example:

https://stackoverflow.com/questions/21117021/bulk-collect-into-and-execute-immediate-in-oracle

 declare
   type x is table of t.id%type index by pls_integer;
   xx x;
  begin
   execute immediate
   'select id from t' bulk collect into xx;
   dbms_output.put_line(xx.count);
 end;
 /

Ok, another example, with the 12c WITH function:

with function f1 (p_a number) return number
as
  l_result number;
begin
  execute immediate 'select count(*) from dba_objects where object_id = :B1'
  into l_result
  using p_a;
  return l_result;
end;
select f1(object_id) from dba_objects where object_name = 'HELP';
/

F1(OBJECT_ID)
-------------
            1


Implementing User Privacy Settings with MySQL Database I am actually working on a social website. Not big, aimed at a very small group of folks. And I am curious, to how I can best implement Privacy settings for my users in a MySQL driven database.
For example, on websites, like, Facebook, Google+, and probably other social websites, you find privacy settings and users can set these for their objects (e.g. content they share).

My issue isn’t writing the code in the language I am doing it in, but my issues are actually structuring up a DB schema that allows me to manage users’ settings. I have never done something like this, and would appreciate any help; also it must be scalable to some extent. Thanks a lot.

This is a JSON output I found from some website, and it looks like each part: allow, deny, description, friends, and value are all columns. However, in MySQL, you cannot really store large amount of comma-separated values, there's simply no space in a single column to do so.

{
  "privacy": {
    "allow": "",
    "deny": "299716926803603",
    "description": "Contacts; Except: Restricted",
    "friends": "ALL_Contacts",
    "value": "CUSTOM"
  },
  "created_time": "2015-09-04T13:01:40+0000",
  "id": "100002958951118_777860722322552"
}

I did something like this, but it simply doesn't work.

Privacy table:

allow       string
deny        string
description string
friends     string
id          int
object_id   int
owner_id    int
value       string

Not sure about this Privacy Table sample.

I'd appreciate a good solution, that is applicable, and most of the users in this community will benefit from this post.

There is a spectrum of options open to you, depending on how much normalisation you desire, how much denormalisation you're prepared to live with, the use cases you have currently and what future functionality you reasonably expect to support.

The most trivial implementation would be to treat your JSON as a blob and store it accordingly, in a VARCHAR column. The application takes care of constructing the JSON and parsing it.  MySQL acts as a glorified key-value store. This will be easy to implement. It is likely to scale well. There will be no flexibility of usage, no built-in data integrity and ad hoc queries will be awful.

Next from this is using a column of type JSON. The DBMS has insight into the content of the JSON and can optimise with indexes, more compact storage and validation etc. It is not what a relational database is optimised for, however, so is unlikely to be your best bet.

I include these two for completeness.  Neither of them use the relational nature of MySQL. If you were to go this route you would be better adopting one of the many fine open source NoSQL solutions available.

To use the relational nature of your chosen product you will need to normalise your data.  The given examples and business rules are sparse so I'll infer and extrapolate somewhat, but I believe my model will cover the essential aspects.

Obviously there will be an entity type called "User", with some large integer type as the primary key.  I'll call this UserID.

All the different things you want to control visibility of will likely have their own representation.  I'd suggest you use the supertype/ subtype pattern. Amongst other benefits this will abstract the security from other concerns and allow addition of new types of interaction without changing this model.  I'll call the root type for all these types of things a Posting and give it a surrogate key PostingID.

Your example image has five categories of visibility, one of which is Custom. I'd suggest an entity type to cover this list. Let's call it Visibility. There is a question of how the system is to reflect changing friendships.  If the Posting is visible only to Friends, and a new friendship is created, should that new friend see the old posting or should it remain shown only to those who were friends at the point of posting? Similarly when unfriended does the miscreant loose rights to see items? I'm assuming the dynamic option is what is preferred.

It is tempting to form an intersection of these three to hold rights to specific postings. This would get very large very quickly, however, and would have substantial write activity reflecting changing friendships.

Better would be to hold the Visibility as an attribute in Posting (a foreign key column) and the corresponding UserFriend list for each User. It may be possible to combine the "Friends" and "Friends except Acquaintances" lists with a flag to distinguish the latter, depending on your specific rules. I'm assuming that friendship is defined as mutual so if A is a friend of B then B is by definition a friend of A. This will double the number of rows in UserFriend but will simplify the semantics.

A separate entity type will be reqired when Visibility has a value of "Custom". This will list the specific UserID values for that PostingID.

From this the tables will be

User
  UserID

UserFriend
  UserID    -- FK to UserID; the owner of this list
  FriendID  -- FK to UserID; the friend's UserID

Visibility
  VisibilityID

Posting
  PostingID
  PostedBy      -- FK to UserID
  VisibilityID

PostingUser
  PostingID
  VisibleTo    -- FK to UserID; the friend who can see this posting

To construct a list of a user's friends' recent activitis one would read the given user's UserFriend rows, join to Posting on the friends' UserIDs, where the posting visibility was "public", "friends" or "custom". For those that have a custom list, join to that on PostingID and the given UserID.

Assuming the symantics of "deny" are "all my friends except this one" I'd implement this by adding a flag to PostingUser to differentiate allow and deny.

You can take a step back along the spectrum of options by denormalising the above.  For example, PostingUser could be stored as a comma list in Posting, as UserFriend could be in Friend.  This would reduce the IO and, likely, the elapsed time. The trade-off is that more of the work has to be done in the application instead of the DBMS.

Although Facebook famously uses MySQL the above is assuredly not how they do this.  They most certainly have deployed many man-years of custom coding, caching, sharding and other tricks to make this work at their scale.

As a final point I would mention solving these sorts of problems are exactly what graph databases are for. Dispense with MySQL and implement this in, say, Neo4J.


MySql - unique key not working I have the following table:

CREATE TABLE `timeline_lists` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `list_type` int(11) NOT NULL,
  `s_object_id` int(11) NOT NULL DEFAULT '0',
  `group_id` int(11) DEFAULT '0',
  `new_items_count` int(11) DEFAULT NULL,
  `last_accessed_on` datetime DEFAULT NULL,
  `last_updated_on` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`,`user_id`),
  KEY `unique_index` (`user_id`,`list_type`,`s_object_id`,`group_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

every time I run the following query a new record is added:

INSERT INTO `timeline_lists` (`id`,`user_id`,`list_type`,`s_object_id`,`group_id`,`new_items_count`,`last_accessed_on`,`last_updated_on`,`created_at`,`updated_at`)
VALUES
(NULL,18724,11,0,0,5,'2014-09-23 16:37:46',NULL,'2014-09-23 16:37:48','2014-09-23 16:37:48')
ON DUPLICATE KEY UPDATE
`timeline_lists`.`new_items_count`=VALUES(`new_items_count`),
`timeline_lists`.`last_accessed_on`=VALUES(`last_accessed_on`),
`timeline_lists`.`updated_at`=VALUES(`updated_at`)

I would expect that the unique index effect and the fields would just get updated instead, if I tun this query 5 times I would get 5 records in the DB instead of just 1.

what am I missing here?

There's no UNIQUE key on your table, only a PRIMARY KEY on an AUTO_INCREMENT column, which never trigger the ON DUPLICATE KEY because, as you probably already know, you're passing id = NULL.

Change this:

KEY `unique_index` (`user_id`,`list_type`,`s_object_id`,`group_id`)

to this:

UNIQUE INDEX `unique_index` (`user_id`,`list_type`,`s_object_id`,`group_id`)


Exporting an Excel Spreadsheet containing a pivot table and charts to MySQL I am looking at moving a large Excel spreadsheet to an SQL database  - most likely MySQL.

The excel spreadsheet contains 12 worksheets, with one of them being the Master, and the other 11 set up to gather information into separate comparitive tables and charts based on the data in the Master worksheet.

The master worksheet is a pivot table containing 90 columns and over 3000 rows. In the past, User's have tried to access this file at different times to edit it as it is on a network drive. Obviously, this not an ideal solution and I would like to set up this spreadsheet on a database.

From my brief research so far, I have seen that phpMyAdmin is a popular solution to export an excel worksheet, saved as a .csv file to a MySQL database. The solution would need to leave the data very accessible to a handful of users who have good excel skills but almost no programming knowledge.

I am wondering if the best solution is to move the entire spreadsheet to a database - ie. Convert the Master worksheet to a .csv file and export it using phpMyAdmin (Is it easy to set-up pivot tables again once this data is exported?), and then on MySQL, create the tables and charts that are currently on our other worksheets so that users can view and add/take from them using phpMyAdmin, or is there a better solution that would save me having to create the other 11 worksheets on MySQL? Or could the MySQL add-in for Excel be used in some way to generate a local copy of the data and other worksheets on everyone's local drive?

My knowledge of databases is elementary to say the least, so any help at all would be greatly appreciated.

It's possible, but it will take some work.  "Unpivoting" sounds like part of the task.

I would dump everything in .csv files, then LOAD DATA them into MySQL into tables that mimic the spreadsheet sheets.

Then, I would construct the tables that make more sense in the database.  An example is the unpivoted version of the data that is spread across 90 columns.

Then I would write various INSERT ... SELECT ... statements to copy the data from the Excel-centric layout to the MySQL-centric schema.  It is the sort of thing where errors are quickly rectified by DROPping the new tables and rerunning the conversion queries.  Plan on doing that a lot until you get the tables looking good.

Then, write and run queries to use the data in the database-centric format.  You should try writing the queries earlier to get a feel for what you will be getting into.


Why does sql server prefer the nonclustered index over the clustered index? I am trying to speed up a table and as I was experimenting I ran into this (what I think is) odd occurrence. I created a clustered index and a nonclustered index that should be the same thing. However, as I have run queries against the table I have found that SQL Server always wants to use the nonclustered index instead of the matching clustered index. On top of that, when needed SQL Server will properly do an index seek on the nonclustered index, but will always perform a scan on the clustered index.

Why does SQL Server prefer the nonclustered index?

And how can I rewrite this so I still have the performance increase but only the clustered index?

I have the following table structure:

CREATE TABLE [dbo].[Variables](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [Header] [varchar](255) NULL,
    [FullVariables] [varchar](max) NULL
)

Clustered index:

ALTER TABLE [dbo].[Variables] ADD  CONSTRAINT [PK_Variables] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)

Nonclustered index:

CREATE UNIQUE NONCLUSTERED INDEX [NonClusteredIndex-20190307-091011] ON [dbo].[Variables]
(
    [ID] ASC
)
INCLUDE (   [Header],
    [FullVariables])

My current knowledge leads me to believe that in this case both of those indexes should contain the data laid out in the same fashion with [ID] being the key column and then [Header] and [FullVariables] as extra data contained on the index instead of being pointers. If you have some source of knowledge that you could link I am more than eager to read more.

I should specify that I don't always want a seek and I understand that a scan is better in some cases (otherwise why would be have it). The table contains about 60GB of data due to row size (several million) multiplied by the varchar(MAX) (which contains strings that are 16000+ characters long). Before inserting into the table, a scan is done to ensure no duplicates are insterted (matching on Header for elimination and on FullVariables). Then the table is joined in several views on the ID field where the seeks are desired.

If SQL Server has two indexes to choose from, both of which satisfy ("cover") the query and provide the best possible path to locating and/or sorting the rows, you should consider it to be a coin flip. It's not, though... I believe there was some research done here (maybe by me, here and here)  that showed it picked the most recent one created or first one alphabetically or something that is otherwise arbitrary.

However, if the coin flip as we'll call it involves the choice between a non-clustered index and a clustered index, and again both indexes properly satisfy the query, SQL Server will always lean toward the non-clustered. Why? Because it's guaranteed to be no wider than the clustered index. The edge case where it is exactly the same width as the clustered index is not a consideration.

You should look at the costs involved with each execution plan, and confirm that the costs SQL Server estimates for the non-clustered index are <= those for the clustered index. If you can show a counter-example, where the non-clustered index is chosen even though its estimated costs are higher than the clustered, please do.


Where can I get SQL Server Transaction Begin Time from? I am working with temporal tables in SQL Server and the generated time is the transaction begin time in UTC datetime2

Is there a function or table in SQL Server to get that same transaction begin time to use elsewhere that does not require me to write to and select from a system versioned table first?

I have some non-system versioned tables that are part of the same transaction and I would prefer if the datetime2 recorded against them matched instead of using SysUTCDateTime and having it vary.

I tried to pull it from sys.dm_tran_active_transactions but its datetime and local server time and also appears to be different ?

system versioned:   2017-04-11 14:00:59.4690673

active transations: 2017-04-11 15:00:59.467

You can get the id of the current transaction from sys.dm_tran_current_transaction.
Then you can get the transaction_begin_time column from sys.dm_tran_active_transactions.
So, use one of the following T-SQL statements:
SELECT transaction_begin_time = dtat.transaction_begin_time
FROM sys.dm_tran_current_transaction dtct
    INNER JOIN sys.dm_tran_active_transactions dtat
        ON dtct.transaction_id = dtat.transaction_id;

or
SELECT transaction_begin_time = dtat.transaction_begin_time
FROM sys.dm_tran_active_transactions dtat
WHERE dtat.transaction_id = CURRENT_TRANSACTION_ID();

CURRENT_TRANSACTION_ID() returns the value of the transaction_id column from the sys.dm_tran_current_transaction.


How would I select distinct records from the same table on two different databases? I have two different databases that both contain a table with the same structure. I have to go across and select the distinct records across both, how is this best achieved? The uniqueness is based on Id. The other columns in the table could have varying data, but Id should be used as the basis of the distinct.

Structure is:

[Media_One].[dbo].[Video]
[Media_Two].[dbo].[Video]

SELECT DISTINCT col1, col2, ...
FROM
(
  SELECT col1, col2, ... FROM Media_One.dbo.Video
  UNION ALL
  SELECT col1, col2, ... FROM Media_Two.dbo.Video
) AS x;


What should be the relationship between an adminstrators table and users table If admin account can create user accounts, does this mean that the relationship between the admin table and the users table is:

ADMIN OneToMany Users

For example, in my project:

A person with the privilege adminstrateur account, can create accounts with the privilege technicien_sav. Obviously, each kind of privilege grants the one who has it access to different parts of the app.
However, I do not see the SQL relationship necessary for the overall functioning of my app. I.e: The administrateur can create technicien_sav accounts even without the existence of the relationship between the two.
What do you think?
PS: It's been years since I've worked with SQL databases, I hope the question is not too naive.

From what I can tell from your image (please post ddl instead), there is no relationship between adminstrateur and technicien_sav. If you add an attribute created_by or administered_by in technicien_sav, there would be a relationship.  Since this attribute most likely is not part of the key for tech... , it would be referred to as a non-identifying relationship. Example:

CREATE TABLE adminstrateur
( adminstrateur_id ... not null primary key
, fullname ...
, ...
);

CREATE TABLE technicien_sav
( technicien_sav_id ... not null primary key
, ...
, adminstrateur_id ... not null
      references adminstrateur (adminstrateur_id)
);

I prefer using the same attribute name throughout the model, names like id, name, etc are IMO to vague. Even better is to use names from the domain of discourse, but I have no idea what the might be in your case.


Details of transactions in SQL SERVER 2008 R2 I need following details:

Username of the user who initiated the transaction
Origin of the query  i.e. Whether Query executed in SQL server New
Query Interface or Transaction due to editing in Table or Via any
other application.

I know that all the transactions are saved in .ldf file.
Using sql query/other tools i was able to fetch some details but not all the above for all transactions.
Is there any way to get all the above information for all transactions.

Use SQL Server Audit, after upgrading to a supported version of SQL Server.  For SQL Server 2008 there's only SQL Trace.  And in either case there's no way to get this information after the fact.  You have to set something up to capture it.


How to set the sequence to the last used id for all tables in Oracle? I'm working on an open source elearning system called Moodle which usually uses MySql or Postgresql, but can also use MSSQL and Oracle.

A client wishes to use Oracle, which I have limited experience of. I've got a local install of Oracle 11g r2.

I used a script to transfer data from MySql into Oracle, including the ID to keep the references.

But now when creating a record I get the error message : ORA-00001: unique constraint (SCHEMANAME.TABLENAME_ID_PK) violated

I'm guessing the sequences weren't updated when I inserted the ID's?

Is there an easy way to update all the table sequences to the last ID number used + 1 in each table?

Unless it's something else?

UPDATE : I can use this to get the last number in the sequence

SELECT sequence_name, last_number
FROM dba_sequences
WHERE sequence_owner = 'SCHEMA_NAME'
AND sequence_name LIKE 'V_%'

The last_number column has values but it looks like they are lower than the last id used in the table.

UPDATE 2: Found some existing code in Moodle that resets the sequence, I had wrongly assumed the naming convention for the sequences was created by Oracle but its Moodle. Here is some Moodle/PHP code that will reset all the sequences:

$todb = moodle_database::get_driver_instance($dbtype, $dblibrary);
$todb->connect($dbhost, $dbuser, $dbpass, $dbname, $prefix, $dboptions);
$totables = $todb->get_tables(false);
$tomgr = $todb->get_manager();

foreach ($totables as $table) {
    mtrace($table);
    try {
        $tomgr->reset_sequence($table);
    } catch (moodle_exception $e) {
        // There are a couple of temporary tables without indexes.
        mtrace($e->getMessage());
        continue;
    }
}

UPDATE 3 : This is basically what the reset sequence function does

$value = (int)$this->mdb->get_field_sql('SELECT MAX(id) FROM {'.$tablename.'}');
$value++;

// Calculate a sequence name from the tablename.
$seqname = $this->getSequenceFromDB($xmldb_table);
if (!$seqname) {
    // Fallback, seqname not found, something is wrong. Inform and use the alternative getNameForObject() method
    $seqname = $this->getNameForObject($table, 'id', 'seq');
}

$this->mdb->execute("DROP SEQUENCE $seqname");
$this->mdb->execute("CREATE SEQUENCE $seqname START WITH $value INCREMENT BY 1 NOMAXVALUE CACHE $this->sequence_cache_size");

First you have to find out what the current sequence value is and then reset it. I would write a script which does the following tasks:

Get the max value from the table
update/recreate the sequence

The script could look like this:

declare
  maxval number;
begin
  select max(id) into maxval from <table_1>;
  execute immediate 'ALTER SEQUENCE <sequence_name_1> START WITH '|| maxval+1 ||';';

  select max(id) into maxval from <table_2>;
  execute immediate 'ALTER SEQUENCE <sequence_name_2> START WITH '|| maxval+1 ||';';
end;
/

I'm pretty sure there are better scripts but if you have to do this task frequently this might do the job without much development time.


Use ANY/ALL to retrieve customer who placed the highest number of orders I have this tables, and i would like to retrieve the customer who placed the highest number of orders.

i wrote this query:

select * from customers where cust_id=(
select cust_id from orders
group by cust_id
having count(*)>=all(select count(cust_id)
                     from orders
                     group by cust_id))

I know that maybe there is a better way to do it, but I was surprised to find that 'ALL' works with '> ='

In my understanding, 'ALL' checks whether the current row is greater or less than all rows in the sub-query, but I never thought it was possible to use it with '='.

If i use it with '=' or '>'  the query doesn't return me any rows as i was expecting.

But if i use them together '>=' the query gives me the correct results.

Isn't it strange?

Anyway, at the end i wrote this query:

SELECT *
FROM Orders, customers
WHERE orders.cust_id=customers.cust_id
and orders.cust_id IN
            (SELECT TOP (1) o.cust_id
            FROM Orders AS O
            GROUP BY O.cust_id
            ORDER BY COUNT(*) DESC);

Do you have a better or more elegant solution?

And isn't it strange the use of '>=' in 'ALL' clause?

Thank you.

Various ways to write. Your query, slightly rewritten to use JOIN and corrected to return all customers in case of ties:

select c.*, o.*
from customers as c
    join orders as o
      on o.cust_id = c.cust_id
where c.cust_id in
      ( select cust_id
        from orders
        group by cust_id
        having count(*) >=
               all ( select count(*)
                     from orders
                     group by cust_id
                   )
      ) ;

Similar, without using >= ALL:

select c.*, o.*
from customers as c
    join orders as o
      on o.cust_id = c.cust_id
where c.cust_id in
      ( select cust_id
        from orders
        group by cust_id
        having count(*) =
                  ( select top (1) count(*) as cnt
                     from orders
                     group by cust_id
                     order by cnt desc
                  )
      ) ;

The subquery simplified using TOP WITH TIES:

select c.*, o.*
from customers as c
    join orders as o
      on o.cust_id = c.cust_id
where c.cust_id in
      ( select top (1) with ties m.cust_id
        from orders as m
        group by m.cust_id
        order by count(*) desc
      ) ;

All of the above could be rewritten to use a derived table or CTE and JOIN as the subquery is uncorrelated.

Another way would be to use window functions in combination with TOP:

with
  d as
  ( select c.*, o.*,
           cnt = count(*) over (partition by o.cust_id)
    from customers as c
        join orders as o
          on o.cust_id = c.cust_id
  )
select top (1) with ties
    d.*
from d
order by d.cnt desc ;

or just window functions:

with
  d as
  ( select m.cust_id,
           cnt = count(*)
           max_cnt = max(count(*)) over ()
    from orders as m
    group by m.cust_id
  )
select c.*, o.*
from customers as c
    join orders as o
      on o.cust_id = c.cust_id
    join d
      on d.cust_id = c.cust_id
where d.cnt = d.max_cnt ;


When to Disable and Recreate Indexes in Data Warehouse ETL? We are moving data from Staging Database into Kimball Data Warehouse DB.
I am hearing different ideas regarding when to disable and rebuild indexes.
Stage tables are truncated every day for each new load. Lets assume we have a customer transaction table, 20 million rows, 5GB. Initial load is 20 mil, and incremental is 100,000 rows everyday (less than 5%)

I am reading its best to disable indexes on new batch fresh loads.

(a) If stage tables are truncated everyday, for Stage: Should I disable index and reenable everyday?

Alter index all on dbo.[StageTable] disable

Insert Into StageTable values (CustomerId, SalesDate, SaleAmount)...

Alter index all on dbo.[StageTable] rebuild

In the actual data warehouse,

(b) Should I reenable and rebuild index for initial load. Should I do this for incremental loads also which are minor?

Alter index all on dbo.[DWTable] disable

Insert Into StageTable values (CustomerId, SalesDate, SaleAmount)...

Alter index all on dbo.[DWTable] rebuild

The answer depends on a number of factors:

What is your goal:  Minimal I/O?  Minimal time to load?  Minimal effort to set up?  24/7 query performance?
How heavy are the indexes: Just one index with on ID column won't make much difference, but several indexes with lots of columns will make a significant difference.
What is the bottleneck?  If the data load is coming across the network, writing to disk may not be the bottleneck if you're writing to a high-performance SSD subsystem.  If you can write the tables and indexes as fast as it comes across the wire, then there's no way you'll make is faster.

So determine what your goal is, and then test each method.  For a 5 GB load, it should be a quick test.

Having said all that, for a 5 GB load I wouldn't bother trying to optimize it.  SQL Server can write that in just a few seconds.  Pick a strategy and run it--if it takes two minutes, then you can only make it about 2 minutes faster--is it worth your time?  If you're approaching this as a learning exercise, do it with a significant amount of data--something that will take 30 minutes.


How to backup/restore SQL Server database from command line similar to mysql I need to automate the migration for some databases from an older server. I know that script a tool using C# for example can be a possibility but I need a simple, fast and effective solution in order to build a batch file to do the job.

Create a .sql file with your backup / restore statement(s) and use SQLCMD

About halfway down that article there is an example of doing a backup.

You can refer to the following pages with information on the T-SQL for doing backups and restores:

Backup
Restore


What is a "Partial Matching Index"? I'm trying to learn more about the "foreign key references check" query plan operator introduced in SQL Server 2016. There's not a lot of information about it out there. Microsoft announced it here and I blogged about it here. The new operator can be seen by deleting a row from a parent table with 254 or more incoming foreign key references: dbfiddle link.

There are three different counts displayed in the operator details:

Foreign Key References Count is the number of incoming foreign keys.
No matching Indexes Count is the number of incoming foreign keys without a suitable index. Verifying that the updated or deleted table won't violate that constraint will require a scan of a child table.
I don't know what Partial Matching Indexes Count represents.

What is a partial matching index in this context? I wasn't able to get any of the following to work:

Filtered indexes
Putting the foreign key column as an INCLUDE column for an index
Indexes with the foreign key column as the second key column
Single column indexes for multiple column foreign keys
Creating multiple covering indexes to enable an "index join" plan for a multiple column foreign key

Dan Guzman pointed out that multiple column foreign keys can match indexes even if the index keys are in a different order than the foreign key columns. His code is here in case someone is able to use it as as starting point to figure out more about partial matching indexes.

I spoke to people much smarter than I and we will be documenting this soon™.

The actual definition of this, in the interim is:

  PartialMatchingIndexCount reflects the number of references that could
  be checked using index seeks, but the index key does not cover all the
  columns being checked. For example, the corresponding
  ForeignKeyReferenceCheck elements contain both a Seek Predicates and a
  Predicate element.

In addition:

  If this number is greater than 0, there is a potential performance issue, in case the partial matches result in large numbers of rows.


Mobile node in a mongodb replica set Since a use case for a mongodb replica set is a base for analyses and learning cases (i.e a read-only database), is it possible/advisable to, in a replica set mongodb network, have an instance running on my laptop that will sync when connected to the net and then act as an offline read only set when not?

To put a point on it,

will this replica set be stable when the laptop is put to sleep/opened/etc?
will the intermittent availability of this set jeopardize an otherwise robust replicated database (say, with one primary, two arbiters and always on secondaries)?

Other questions that touch upon this but don't answer it directly (AFAIK) are

MongoDB - Replica sets - Syncing and DC issue
Sync MongoDB Replica set with Remote Replica Set

In these cases setting the arbiter correctly seems to do the trick, so maybe my crazy idea would work?

Thanks in advance.

Since a use case for a mongodb replica set is a base for analyses and learning cases (i.e a read-only database), is it possible/advisable to, in a replica set mongodb network, have an instance running on my laptop that will sync when connected to the net and then act as an offline read only set when not?

This configuration is possible, but generally not advisible.

  1) will this replica set be stable when the laptop is put to sleep/opened/etc?

The normal rules for fault tolerance apply, so assuming your replica set has a majority of configured voting members available it's fine for one or more members to go offline.

The replica set node on your laptop will also still be usable as a readonly secondary (assuming you use a secondary read preference with your driver or rs.slaveOk() in the mongo shell).

  2) will the intermittent availability of this set jeopardize an otherwise robust replicated database (say, with one primary, two arbiters and always on secondaries)?

This is where the approach heads into "not advisible" territory. In theory you can get a workable configuration with a core of stable voting members in your replica set configuration plus your laptop configured as a hidden, non-voting secondary.

However, there are a number of caveats to consider:

All replica set members need to be visible to each other via the same details as configured in your replica set configuration (hostnames and ports). You need to ensure your laptop knows what its expected hostname should be (otherwise it may not be able to find itself in the replica set config) and you need to consider how all the members will securely communicate with each other (eg. VPN).
Replica set members will be noisy about failed communication with unavailable members so you may have a lot of log noise that could potentially mask real networking problems.
If you fudge hostname resolution (eg. by editing /etc/hosts) it's very easy to introduce typos or forget to include this when updating or adding other members in the replica set.
If your laptop has incomplete or conflicting views of the network connectivity to other members, this may cause issues with elections/failover depending on your version of MongoDB and how you have configured your replica set.
Depending on the write activity for your replica set and how long your laptop remains offline, the laptop rejoining may cause noticeable load when catching up on old data that isn't in the current working set.
If your laptop remains offline for too long, it may require a full resync if there are no longer any oplog (operations log) entries in common with another member of the replica set.

Recommended approaches

1) Setup a hidden secondary in your hosted environment, instead.

2) If you really want a locally replicated copy and your replica set environment is anything other than development, I would suggest an alternative approach that sidesteps some of the potential issues with networking and visibility: you could consider using Mongo Connector. This is not an officially supported tool, but what it provides is replication-like behaviour to one or more target systems. It also doesn't remove caveats about possibly adding additional load or requiring a resync. For more background, see: Mongo Connector - Usage with MongoDB.

  an otherwise robust replicated database (say, with one primary, two arbiters and always on secondaries)?

FYI, you should only have zero or one arbiter. The arbiter is a member that only exists to potentially cast a tie-breaking vote where you have an even number of voting replica set members. The presence of one (or especially more than one!) arbiter already compromises robustness in the event of failover. An arbiter helps with voting majority but cannot contribute to write majority -- this leads to asymmetric failover scenarios. For example, in a Primary-Secondary-Arbiter configuration if either data bearing node fails you will still have a Primary but no longer have active replication or an ability to acknowledge more than a w:1 write concern.


MySQL - Disabling the innodb_log_file Innodb by default writes a log file named ib_logfile0, ib_logefile1 etc..

I can see that this writes are taking a lot of disk resources at my server, My question is, are this logfiles a must? or can i disable them by anyhow? or maybe I shouldn't?

As per the Oracle documentation, by default, InnoDB creates two redo log files (or just log files) ib_logfile0 and ib_logfile1 within the data directory of MySQL.
In MySQL versions 5.6.8 and above, the default size of each redo log file is 48MB each. This can be configured by the user by making use of innodb_log_file_size server option.

The generated redo log record will contain information necessary to repeat the same operation later during a database recovery. So the redo log record will contain information about the original set of pages and what has been changed in them. Using the redo log record the set of dirty pages will be re-created during database recovery.

So, as shortly I am explaining, to maintain the log sequence number(LSN) in Innodb, it is important.

For your Ref Redo Logging in InnoDB


MSSQL - How to support aggregate pushdown on indexed view with SUM(decimal) I have an indexed view with non-clustered columnstore index, e.g.

CREATE VIEW [dbo].[SalesAggregated] with schemabinding AS
        SELECT
            BusinessDate,
            ItemId
            ,COUNT_BIG(*) AS NumberOfRows
            ,SUM(Gross) AS Gross
        FROM [dbo].[myTable]
        GROUP BY BusinessDate, ItemId

Where Gross in underlying table is defined as decimal(18,6), so it supports aggregate pushdown. But result of the SUM in the view is decimal(38,6), which is too big for aggregate pushdown.

I can't cast it by using CAST(SUM(Gross) as decimal(18,6)) because that's not supported in indexed views, I get this error when I try to create unique clustered index:

  Cannot create the clustered index 'UX_SalesAggregated' on view
  'DemoDB.dbo.SalesAggregated' because the select list of the view
  contains an expression on result of aggregate function or grouping
  column. Consider removing expression on result of aggregate function
  or grouping column from select list.

Converting fields to money datatype in underlying table seems like one of the possible solutions, but I can't do that with all fields as some of them requires higher precision than 4 decimal points.

I also need the automatic indexed view matching working, so that the indexed view is automatically used if possible when the underlying table is queried. That probably wouldn't be possible when I would create a view that casts decimals to money in between the table and indexed view.

For example, I want to achieve that queries like

SELECT SUM(Gross)
FROM myTable
WHERE BusinessDate = '2019-08-14';

Will use columnstore index scan on the indexed view and that the aggregation will be done within this scan. That isn't now possible because the Gross in SalesAggregated is decimal(38,6) which doesn't support aggregate pushdown. As a result, query optimizer (incorrectly) rather scans columnstore from unaggregated table where aggregate pushdown is possible.

Is there any solution how to achieve that?

UPDATE

So the problem is only when the PK column from the underlying table isn't part of the GROUP BY in indexed view definition. Example below from Randi works, but following code fails on the error mentioned above:

CREATE TABLE [dbo].[myTable](
    [ItemId] [int] PRIMARY KEY NOT NULL,
    [BusinessDate] [date] NULL,
    [Gross] [decimal](18, 6) NULL
);

GO

CREATE VIEW [dbo].[SalesAggregated] with schemabinding AS
        SELECT
            BusinessDate
            ,COUNT_BIG(*) AS NumberOfRows
            ,CAST(SUM(Gross) as [decimal](18, 6))  AS Gross
        FROM [dbo].[myTable]
        GROUP BY BusinessDate
GO

CREATE UNIQUE CLUSTERED INDEX CX_BusinessDate ON dbo.[SalesAggregated](BusinessDate);

CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_SalesAggregated] ON [dbo].[SalesAggregated]
(
    [Gross],
    BusinessDate
);

If you can change the query itself to use the view + NOEXPAND, this can work:

Table definition and test data

CREATE TABLE [dbo].[myTable](
    [ItemId] [int] PRIMARY KEY NOT NULL,
    [BusinessDate] [date] NULL,
    [Gross] [decimal](18, 6) NULL
    );

INSERT INTO
[dbo].[myTable] WITH(TABLOCK)
(BusinessDate,Itemid,Gross)
SELECT
TOP(5000000) CAST(getdate() AS DATE),
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
11.2142
FROM master..spt_values
CROSS APPLY master..spt_values spt2;

View definition + indexes

CREATE VIEW [dbo].[SalesAggregated] with schemabinding AS
        SELECT
            BusinessDate,
            ItemId
            ,COUNT_BIG(*) AS NumberOfRows
            ,CAST(SUM(Gross) as [decimal](18, 6))  AS Gross
        FROM [dbo].[myTable]
        GROUP BY BusinessDate, ItemId
GO

CREATE UNIQUE CLUSTERED INDEX CX_ItemId
ON dbo.[SalesAggregated](ItemId);

CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_SalesAggregated] ON [dbo].[SalesAggregated]
(
[Gross],
BusinessDate

);

Query

SELECT SUM(Gross)
FROM [dbo].[SalesAggregated] WITH(NOEXPAND)
WHERE BusinessDate = '2019-08-14';

Result

Properties of the scan with the locally aggreggated rows & batch mode:

Querying the view + WITH(NOEXPAND) is necessary to make this example work.

There are many other reasons why you should use WITH(NOEXPAND). More reasons in this blogpost by Paul White


How are database backups conducted using SQL Server VSS Writer Service We have few servers where backups are running from an unknown source. The physical_device_name in the backupmediafamily table is something like {922DDBA9-0686-46B8-94BC-XXXXXXXXXX}2.

I was checking randomly and noticed this kind of backup was running on the database. So I stopped SQL Server VSS Writer service and the backup process stopped. It prompted me to think that backups are being also being conducted by this service. I am still unaware on how Windows/SQL decides to trigger these backup at particular times only since the SQL Server VSS Writer service is running 24*7.

I have also seen cases on few servers where SQL Server VSS Writer is running but those backups are not happening at database level.

Can someone please help me in understanding (identifying) what is causing SQL Server to trigger such backups as I want to stop them across entire organisation.

You will need to identify the Backup strategy which would be using the VSS Writers for taking backups/Restore.

In our environment we had Netbackup and HPDP (Data Protector). While for Netbackup the location would be the master media location ,for HPDP it used to be similar to what you have {922DDBA9-0686-46B8-94BC-XXXXXXXXXX}.

HPDP uses VSS Writers for the backups and it is what was in our case. It can differ in your situation but there is some backup agent which is takin it.


Use data from Mysql to ElasticSearch with Logstash I'm using logstash for use my mysql database in ElasticSearch

My conf is the next.

input {
    jdbc {
        jdbc_connection_string => "jdbc:mysql://[ip]:3306/nextline_dev"
        jdbc_user => "[user]"
        jdbc_password => "[pass]"
        #schedule => "* * * * *"
        #jdbc_validate_connection => true
        jdbc_driver_library => "/path/mysql-connector-java-6.0.5.jar"
        jdbc_driver_class => "com.mysql.cj.jdbc.Driver"
        statement => "SELECT * FROM Account"
    }
}
output {
    elasticsearch {
        index => "account"
        document_id => "%{id}"
        hosts => ["127.0.0.1:9200"]
    }
}

But I have some questions, I want to schedule more than one query, but the index will be always account.

Can I make a dynamic index for the output to elasticsearch? And how can I use more than one statement? (Export more than one table)

I found that ELK LogStash 6.X have document_id deprecated, so if I use my configuration without document_id it works.


Best structure for MySQL table I'm designing my table, but I have doubts: I will have two columns A and B that cannot have repetitions and, in the same row, can't both be null.

I don't know if the best thing to do is to use a primary key with a combination of the values of A and B or a completely different column (for example an autoincremented column).

A PRIMARY KEY does not allow NULLs.  But a UNIQUE KEY does.  I suggest you try a simple table that has A and B, each nullable, then INSERT suitable combinations to see if they work.

UNIQUE(A,B) says that the pair is unique, while allowing either column to be repeated.  Your Question is vague--is that what you wanted?

This will not check for "can't both be null"; that would have to be tested for at the application level.

Based on your Comment, you seem to be asking about the WHERE clause of a SELECT, not the table definition.  And it seems like neither column should allow NULLs:

CREATE TABLE ... (
    vat ... NOT NULL,
    ssn ... NOT NULL,
    ...
    PRIMARY KEY(vat),
    UNIQUE(ssn)
)

(or swap the two keys)

Then the application should decide which of these to use:

SELECT ... WHERE vat = ?
SELECT ... WHERE ssn = ?
SELECT ... WHERE vat = ? AND ssn = ?

depending on what is given in the <form>.


Restoring old MySQL database settings I know this question may be similar to some others posted but think it may be slightly different, so hopefully it's ok.

Basically I just reinstalled Windows as I bought a new HD and I am in the process of copying all the MySQL databases back to the new installation dir; however I'm not sure if it's ok to copy all the default MySQL databases back with it? Those being:

cdcol
mysql
performance_schema
phpmyadmin
test
webauth

I assume one of these contain details I want to keep such as info about the users and also other stuff like mysql variables - unless that is found in a MySQL .ini file or something?

I'm not 100% sure but I'm pretty sure I'm still using the same version of MySQL. If it matters I am using XAMPP.

So basically my question is can I copy the old folders above over the new ones and what are these folders/databases used for exactly (other than the obvious phpmyadmin one)?

Let's say on the new server you want datadir to be D:\MySQLData

Simply copy everything and all subfolders from old server's C:\Program Files\MySQL\MySQL Server 5.5\data to the new server's D:\MySQLData. You said you are using XAMPP. RUn this query SHOW VARIABLES LIKE 'datadir';. Copy that folder to D:\MySQLData.

Then add this

[mysqld]
datadir="D:/MySQLData"

in the new server's my.ini

This will work seamlessly if the new server has the same version of MySQL as the old.

If the new server has the next higher major release (5.1 -> 5.5,5.5 - > 5.6) you will then have to run mysql_upgrade --upgrade-system-tables. Make sure you keep a backup.

If you are using InnoDB (in whole or in part), get all the InnoDB settings from the old my.ini over the new server's my.ini.

Give it a Try !!!


PostgreSQL - Querying Second Table with Multiple Results I am working in PostgreSQL 9.6.6 on a query that involves the use of a second table to return additional data points. The table - "events" - is associated with many entries from the second - "activities" - via a foreign key.

Events

| id | name   |
|----|--------|
| 1  | event1 |
| 2  | event2 |
| 3  | event3 |

Activities

| id | name      | startDate              | endDate                | status    | eventId |
|----|-----------|------------------------|------------------------|-----------|---------|
| 1  | activity1 | 2018-08-27 05:00:00+00 | 2018-08-28 05:00:00+00 | Draft     | 1       |
| 2  | activity2 | 2018-09-27 05:00:00+00 | 2018-09-27 10:00:00+00 | Submitted | 1       |
| 3  | activity3 | 2018-08-25 05:00:00+00 | 2018-08-27 10:00:00+00 | Draft     | 1       |
| 4  | activity4 | 2018-08-21 05:00:00+00 | 2018-08-24 05:00:00+00 | Approved  | 2       |
| 5  | activity5 | 2018-09-27 05:00:00+00 | 2018-09-29 05:00:00+00 | Draft     | 2       |
| 6  | activity6 | 2018-10-27 05:00:00+00 | 2018-10-28 05:00:00+00 | Approved  | 3       |
| 7  | activity7 | 2018-08-27 05:00:00+00 | 2018-08-27 10:00:00+00 | Approved  | 3       |

The data points being collected via the event are a startDate, endDate & status based on the associated activities.

startDate => The least startDate of all associated activities.
endDate => The greatest endDate of all associated activities.
status => Status equals "Pending" unless all associated activities
have a status of "Approved" (then status equals "Complete").

An example returned query would look like:

| id | name   | startDate              | endDate                | status    |
|----|--------|------------------------|------------------------|-----------|
| 1  | event1 | 2018-08-25 05:00:00+00 | 2018-09-27 10:00:00+00 | Pending   |
| 2  | event2 | 2018-08-21 05:00:00+00 | 2018-09-29 05:00:00+00 | Pending   |
| 3  | event3 | 2018-08-27 05:00:00+00 | 2018-10-28 05:00:00+00 | Completed |

I have formulated the following with multiple subqueries, but the performance is terrible.

Any suggestions as to how can I improve this to limit (or even remove) the use of subqueries?

SELECT DISTINCT ON("startDate", "event"."name") 1, "event"."id", "event"."name", (
  SELECT "activities"."startDate"
  FROM "events"
  INNER JOIN "activities" AS "activities" ON "event"."id" = "activities"."eventId"
  WHERE "event"."id" = "activities"."eventId"
  ORDER BY "event"."id" ASC, "activities"."startDate" ASC
  LIMIT 1
) AS "startDate", (
  SELECT "activities"."endDate"
  FROM "events"
  INNER JOIN "activities" AS "activities" ON "event"."id" = "activities"."eventId"
  WHERE "event"."id" = "activities"."eventId"
  ORDER BY "event"."id" ASC, "activities"."endDate" DESC
  LIMIT 1
) AS "endDate", (
  SELECT DISTINCT ON("event"."id")
    CASE "activities"."status"
    WHEN 'Draft' THEN 'Pending'
    WHEN 'Submitted' THEN 'Pending'
    ELSE 'Complete'
    END
  FROM "events"
  INNER JOIN "activities" AS "activities" ON "event"."id" = "activities"."eventId"
  WHERE "event"."id" = "activities"."eventId"
  ORDER BY "event"."id" ASC, "activities"."status" DESC
) AS "status"
FROM "events" AS "event"
ORDER BY "startDate" DESC, "event"."name" ASC
LIMIT 20
OFFSET 0;

Test:

SELECT e.name,
       MIN(a.startDate) startDate,
       MAX(a.endDate) endDate,
       CASE WHEN SUM(a.Status != 'Approved')=0
              THEN 'Completed'
              ELSE 'Pending'
         END status
FROM Events e
JOIN Activities a ON e.id = a.eventId
GROUP BY e.name

If fail replace SUM(a.Status != 'Approved') with SUM(CASE WHEN a.Status != 'Approved' THEN 1 ELSE 0 END).


Synchronizing a RDS instance with another RDS instance I have a database on an RDS Instance (master) that needs to be synchronized to another RDS instance (slave). I created a read-replica for the first one, but that is created as read_only so I cannot do any operations on it.

Basically, I'll need to create a few other databases on the slave instance as well so that needs to be non-read-only. I'll also need to setup sync to happen once daily.

I am using MySQL 5.6+.

DISCLAIMER : I haven't tried this myself

What you asking for is somewhat rare. Nevertheless, it is possible.

Here is a link to Looker.com to explain how.

Basically, you do the following:

Create a read replica
Create a Custom DB Parameter group
Tweak the read_only setting in the Custom DB Parameter Group
When replica has been created, ...

you modify the instance
set Parameter Group to the Custom DB Parameter Group

Once you have the accessibility, you can create the DBs directly in the Slave.

I would normally say "Give it a Try !!!" but I never did this before. So, try it out and tell us all if it worked.


How to make ON CONFLICT work for compound foreign key columns? I'm having trouble with ON CONFLICT not working for foreign key columns where the foreign key is compound.   Here's an example.

create table foreign_table (
   id_a text    not null,
   id_b text    not null,
   id   integer primary key,
   constraint ft_a_b_key unique (id_a, id_b)
);

create table my_table (
   id          integer,
   ftable_id_a text,
   ftable_id_b text,
   constraint my_table_a_b_fk
      foreign key (ftable_id_a, ftable_id_b) references foreign_table (id_a, id_b)
);

Using this query:

insert into tcell_test.my_table (id, ftable_id_a, ftable_id_b)
    values (3, 'a3', 'b3') on conflict do nothing ;

where, say, 'a3' isn't in foreign_table, I would expect the ON CONFLICT to handle the error.

Instead I get the error:

[23503] ERROR: insert or update on table "my_table"
   violates foreign key constraint "my_table_a_b_fk"
Detail: Key (ftable_id_a, ftable_id_b)=(a3, b3)
   is not present in table "foreign_table".

Is there a way to correct this so ON CONFLICT handles the error?

I think you may be misunderstanding what a CONFLICT is.  A CONFLICT is a violation of uniqueness, basically the row that is being added should not be added because another row with the same values already exists.

In your example of insert into tcell_test.my_table (id, ftable_id_a, ftable_id_b) values (3, 'a3', 'b3') on conflict do nothing;, the ON CONFLICT condition will never be reached because you have no primary key or unique constraint on my_table:

edb=# alter table my_table add primary key (ftable_id_a,ftable_id_b);
ALTER TABLE
edb=# insert into my_table (id, ftable_id_a, ftable_id_b)
    values (3, 'a3', 'b3') on conflict do nothing ;
INSERT 0 1
edb=# insert into my_table (id, ftable_id_a, ftable_id_b)
    values (3, 'a3', 'b3') on conflict do nothing ;
INSERT 0 0
edb=# select * from my_table ;
 id | ftable_id_a | ftable_id_b
----+-------------+-------------
  3 | a3          | b3
(1 row)

As you can see in the example above, my second insertion into my_table did nothing because there would have been a primary key violation.  If I omit the ON CONFLICT clause, I get:

edb=# insert into my_table (id, ftable_id_a, ftable_id_b)
    values (3, 'a3', 'b3');
ERROR:  duplicate key value violates unique constraint "my_table_pkey"
DETAIL:  Key (ftable_id_a, ftable_id_b)=(a3, b3) already exists.

The message that you mentioned in you original post (ERROR: insert or update on table "my_table" violates foreign key constraint "my_table_a_b_fk") pertains to a Foreign Key violation (not a primary key/uniqueness violation).  This violation occurs when there should be a row in foreign_table with id_a=a3 and id_b=b3, but there isn't.  The database expects this row to exist because you have defined that my_table references foreign_table (in other words, my_table depends on foreign_table).  Because the row doesn't exist in foreign_table, your insertion into my_table fails.  First, insert into foreign_table, and then you can insert into my_table:

edb=# insert into my_table (id, ftable_id_a, ftable_id_b)
    values (3, 'a3', 'b3') on conflict do nothing ;
ERROR:  insert or update on table "my_table" violates foreign key constraint "my_table_a_b_fk"
DETAIL:  Key (ftable_id_a, ftable_id_b)=(a3, b3) is not present in table "foreign_table".
edb=# insert into foreign_table values ('a3','b3',1);
INSERT 0 1
edb=# select * from foreign_table ;
 id_a | id_b | id
------+------+----
 a3   | b3   |  1
(1 row)
edb=# insert into my_table (id, ftable_id_a, ftable_id_b)
    values (3, 'a3', 'b3') on conflict do nothing ;
INSERT 0 1
edb=# select * from my_table ;
 id | ftable_id_a | ftable_id_b
----+-------------+-------------
  3 | a3          | b3
(1 row)


How do i specify a type for a new column through a select statement I'm trying to create a new view from an existing table which as 2 columns:

CREATE TABLE writers (
  movie_id integer NOT NULL REFERENCES movies,
  person_id integer NOT NULL REFERENCES people,
  PRIMARY KEY (movie_id,person_id)
) ;

I'm trying to make a view which adds a new column and a default character value so I want a the new to have the columns  movie_id, person_id, position_name  and I need position_name to be of type character so that I can union this view with another view.

Is this possible to do?

Yes it is possible:

create view ... as (
    select movie_id, person_id, cast(null as char(1)) as position_name
    from writers
);

I assumed that null (which is part of any type) is what is causing you the problem. Replace null with whatever if that is not the case


Mongo DB Backup & restore 0 down vote favorite

I have a DB with 150GB of data. I am using the MongoDump and Mongorestore methods to backup and restore.

My production server is running Mongo 2.2 and my test server is 2.6.1

When I take a back up from the Mongo2.2 production server it's taking a long time to complete. The restoration take 6-8 Hours. It hasn't completed without error. Sometimes the restore is dropped automatically and we need to run the restore again or restore the missed collection.

Is there a better way to to take a backup and restore method, where we can save time and run it without Errors?

Regards,

Rishi

With a single node i don't think you have other option than mongodump and LVM snapshot. In the case you run on a replica set which is recommended for production you can just stop one secondary and copy the data directory.


MySQL: Why am I getting this warning? I am calling the stored procedure below, and get this warning, is this warning a false positive or is my query unsafe?

1 queries executed, 1 success, 0 errors, 1 warnings

Query: call User_Signup('lee3@gmail.com', 'password', 'Lee', 'Brooks', null, null, null, null)

1 row(s) affected, 1 warning(s)

Execution Time : 0.042 sec
Transfer Time  : 0.093 sec
Total Time     : 0.136 sec

Note Code : 1592
Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.

Here is the call:

CALL User_Signup('lee3@gmail.com', 'password', 'Lee', 'Brooks', NULL, NULL, NULL, NULL);

Here is the stored procedure:

DELIMITER $$

USE `MobiFit_Dev` $$

DROP PROCEDURE IF EXISTS `User_Signup` $$

CREATE DEFINER = `root` @`localhost` PROCEDURE `User_Signup` (
  email VARCHAR (250),
  hashedPassword BINARY(60),
  firstName VARCHAR (100),
  lastName VARCHAR (100),
  gender ENUM ('Male', 'Female'),
  dateOfBirth DATE,
  height DECIMAL (3, 2),
  currentWeight DECIMAL (4, 1)
)
BEGIN
  INSERT INTO USER (
    Email,
    HashedPassword,
    RoleId,
    FirstName,
    LastName,
    Gender,
    DateOfBirth,
    Height,
    CurrentWeight,
    CreatedAt
  )
  VALUES
    (
      email,
      hashedPassword,
      (SELECT
        id
      FROM
        Role
      WHERE `Code` = 'CUSTOMER'),
      firstName,
      lastName,
      gender,
      dateOfBirth,
      height,
      currentWeight,
      UTC_TIMESTAMP()
    ) ;
END $$

DELIMITER ;

That warning is quite real and correct. Why??

The warning is meant to have DBA and Developer pick off anachronisms.

What's an anachronism ? Think of these analogies:

Having a cellphone in the year 1930
Using in a LapTop in 1976
Having a Coin with the Year 5 B.C. (would probably be fake)

In essence, it is having an object that exists before its time.

Look at the query. The first part of the INSERT to be evaluated would be the

(SELECT id FROM Role
  WHERE `Code` = 'CUSTOMER')

In a high write DB environment, if the id was inserted into the Role table in one DB connection and you executed the INSERT in another DB Connection, you could get a race condition. How?

If the Customer Role was INSERTed into the Role table first, you get a numeric value for the id
If the Customer Role was INSERTed into the Role table last, you get a NULL for the id

This situation, governed by the order the entries were made, would yield inconsistent results should you ever try to recover from the binary logs. This situation is even more evident when doing Master/Slave Replication because the Slave could theoretically be inconsistent for this reason.

In your particular case, you can breathe a sigh of relief because you, the Developer/DBA, already predefined the role CUSTOMER in the Role table. You need not worry, right ???

Therefore, that warning is just a reminder for everyone to streamline their input of data.

You could ignore that particular warning.

However, for the sake of clarity, or if you want the error log to stop growing due to the warning, you should do this:

STEP 01

Make the Stored Procedure as follows:

DELIMITER $$

USE `MobiFit_Dev` $$

DROP PROCEDURE IF EXISTS `User_Signup` $$

CREATE DEFINER = `root` @`localhost` PROCEDURE `User_Signup` (
  email VARCHAR (250),
  hashedPassword BINARY(60),
  User_Role INT,
  firstName VARCHAR (100),
  lastName VARCHAR (100),
  gender ENUM ('Male', 'Female'),
  dateOfBirth DATE,
  height DECIMAL (3, 2),
  currentWeight DECIMAL (4, 1)
)
BEGIN
  INSERT INTO USER (
    Email,
    HashedPassword,
    RoleId,
    FirstName,
    LastName,
    Gender,
    DateOfBirth,
    Height,
    CurrentWeight,
    CreatedAt
  )
  VALUES
    (
      email,
      hashedPassword,
      User_Role,
      firstName,
      lastName,
      gender,
      dateOfBirth,
      height,
      currentWeight,
      UTC_TIMESTAMP()
    ) ;
END $$

STEP 02

When calling the Stored Procedure, retrieve the role_id and pass it into the Stored Procedure:

SELECT id INTO @GivenRoleID FROM Role  WHERE `Code` = 'CUSTOMER';
User_Signup('lee3@gmail.com', 'password', @GivenRoleID, 'Lee', 'Brooks',
null, null, null, null);

Give it a Try !!!


Manage Postgres user permissions with many databases and users How do you manage users' permissions efficiently when your company has a lot of databases and users?

My company has > 20 reporting databases. Whenever I need to change permission for some users, I need to use psql \dp and fire some queries to alter their roles.

It's ok for now since we only have 5 users, but surely the number would increase and make it very hard to manage it properly via the terminal. Could you suggest me some ways (or tools) to efficiently manage database permissions?

You use roles that have privileges on the objects.

The end users have no privileges assigned, but are members of those roles whose privileges they should inherit.

Then you can simply drop end users, and adding them just means adding them to a couple of roles.


How to know when Postgresql password is changed My question is how to know the date when a postgresql user password is changed.
Is it possible?
I know postgresql has account expiry feature,
but I'm asked to check if users have changed their passwords and to get the date.

Regards,

I tried pg_shadow etc. but they seem to have no date info.

You can't. PostgreSQL does not record the time at which user information was updated.

The internal table storing this info is pg_authid. The only timestamp column there is rolvaliduntil, which does not help you.

The best you can do is determine the transaction ID that changed that user the most recently. This might not be the same as the one that changed the password. You can get this with:

select xmin from pg_authid where rolname = 'theuser';

You can compare this to SELECT txid_current() to get a very rough idea of how long ago it might've been changed, in terms of the server's transaction rate. If your tx rate is very bursty this won't do you any good. It might also be cleared (set to FrozenXID) if transaction ID wrap-around has happened on a busy server. I do not recommend relying on this method for anything even remotely interesting.

You can't determine who performed the transaction that made the change.

I imagine that a patch to add a last-change timestamp to pg_authid would probably be accepted by the community if you were to submit one to PostgreSQL 9.5. Raise this on pgsql-hackers. You will need to know some C programming and be ready to read some documentation, or to pay a developer to write it for you.

For the problem you are working on it would be more typical to have a higher layer in the application keeping track of audit information like this, where users only change their passwords through the app.


How to fix accented character substitutions on French text in MySQL db I'm building a small database for a charity I volunteer with. It will track events in English and French.  When I enter characters like an accented e (as in "journée") it appears in the database as (journÃ©e).  If I can get it in the database properly the é comes out as a black diamond with a question mark (�).

Can anyone tell me how to prevent this?

Here's my table:

CREATE TABLE `dtf_events` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name_en` VARCHAR(50) NULL DEFAULT NULL,
    `name_fr` VARCHAR(50) NULL DEFAULT NULL,
    `description_en` VARCHAR(256) NULL DEFAULT NULL,
    `description_fr` VARCHAR(256) NULL DEFAULT NULL,
    `publish_start` DATE NULL DEFAULT NULL,
    `publish_end` DATE NULL DEFAULT NULL,
    `event_date` DATE NULL DEFAULT NULL,
    `link` VARCHAR(1024) NULL DEFAULT NULL,
    `category` ENUM('operational','fundraising','news','test') NULL DEFAULT NULL,
    `comment` TEXT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=34
;

And here's a sample of the output I get if the proper accented character is in the db.  Note that this code generates a partial page that is intended to fit into an iframe in a text widget on a WordPress site that I have very little administrative control over.

28-f�v-2018 - Date Limite pour demande d'application
24-mar-2018 - Jour d'informations pour volontaire
23-sep-2018 - Journ�e d�orientation: b�n�voles et familles
24-oct-2018 - Journee du Vol 2018mer &middot; 31-d�c-1969 - start is past, end is future

The é in février shows up with a black diamond with a question mark.  That is pulled from a DATE field in the database in YYYY-MM-DD format and converted with the strftime() function:

$friendly_date = strftime("%a &middot; %d-%b-%Y",strtotime($event_date));

You can see the output in the Evènements à Venir section on the right side of the page at http://yow.dreamstakeflight.ca/accueil/  (I had nothing to do with the design of that site ;{})

After you connect to MySQL in PHP OR in HeidiSQL, use SET NAMES 'utf8'. This is effectively equivalent to:

SET character_set_client = utf8;

SET character_set_results = utf8;

SET character_set_connection = utf8;

Now try your insert statement containing French characters.


Oracle 11g TDE wallet autologin I have setup Oracle TDE for my 11.2.0.4 database. I created the wallet. I created the autologin wallet and everything looked good.
{ORACLE_HOME}/bin/orapki wallet create -wallet cwallet.sso -auto_login -pwd password
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

The encryption wallet itself was open:
SQL> select STATUS  FROM V$ENCRYPTION_WALLET;

STATUS
------------------
OPEN

But after I restarted the database the wallet status showed closed and I had to manually open it. So my autologin did not work.
SQL> select STATUS  FROM V$ENCRYPTION_WALLET;

STATUS
------------------
CLOSED

Can anyone explain what could be the problem or what am I missing here? Thanks.

Without knowing what exactly you did, all I can say is it should work, but if you use Grid Infrastructure, you may need some additional configuration.
ORA-28365: wallet is not open when starting database with srvctl or crsctl when TDE is enabled (Doc ID 2711068.1)
One more thing, in the -wallet parameter we specify a directory usually, and not cwallet.sso, which will be generated automatically.
Below just works:
SQL> select * from v$encryption_wallet;

WRL_TYPE             WRL_PARAMETER                                      STATUS
-------------------- -------------------------------------------------- ------------------
file                 /u01/app/oracle/admin/$ORACLE_SID/wallet           CLOSED

SQL> !orapki wallet create -wallet /u01/app/oracle/admin/$ORACLE_SID/wallet -auto_login -pwd Oracle.123
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

SQL> select * from v$encryption_wallet;

WRL_TYPE             WRL_PARAMETER                                      STATUS
-------------------- -------------------------------------------------- ------------------
file                 /u01/app/oracle/admin/$ORACLE_SID/wallet           OPEN_NO_MASTER_KEY

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size                  2254952 bytes
Variable Size             536872856 bytes
Database Buffers         1593835520 bytes
Redo Buffers                4923392 bytes
Database mounted.
Database opened.
SQL> select * from v$encryption_wallet;

WRL_TYPE             WRL_PARAMETER                                      STATUS
-------------------- -------------------------------------------------- ------------------
file                 /u01/app/oracle/admin/$ORACLE_SID/wallet           OPEN_NO_MASTER_KEY


Query using $gte and New Date() working in MongoDB shell but not through python pymongo When I run the following in the mongodb shell, it returns the correct number of records:

(searching for the matching "SourceName" within the last 8 days)

mongos> db.fdl.count({ SourceName : "SRC1" , "Created" : { $gte : new Date(new Date().setDate(new Date().getDate()-8))  }})
482

But when I try to run it through python / pymongo, it returns nothing:

import time
import pymongo

myclient = pymongo.MongoClient("mongodb://mylogin:mypass@mymongo.mydomain.net")
mydb = myclient['fdl']

print mydb.fdl.count({ SourceName : "SRC1" , "Created" : { $gte : new Date(new Date().setDate(new Date().getDate()-8))  }})

myclient.close()

then run it:

python query.py
0

In the python code, if I run the query:

    print mydb.fdl.count({ SourceName : "SRC1" })

Then I do get a value. So the problem comes from the $gte search.

The problem is not with the count() method, because if I use the find() method, the problem is the same: documents returned in the MongoDB shell, but nothing when calling with python.

Any help would be greatly appreciated !
Thanks a lot in advance :-)

As per MongoDB pymongo documentation here

The  count() method is deprecated and not supported in a transaction. Please use count_documents() instead.

count(with_limit_and_skip=False)

DEPRECATED - Get the size of the results set for this query.

The count() method is deprecated and not supported in a transaction. Returns the number of documents in the results set for this query. Does not take limit() and skip() into account by default - set with_limit_and_skip to True if that is the desired behavior. Raises OperationFailure on a database error.

When used with MongoDB >= 2.6, count() uses any hint() applied to the query. In the following example the hint is passed to the count command:

collection.find({‘field’: ‘value’}).hint(‘field_1’).count()

The count() method obeys the read_preference of the Collection instance on which find() was called.

Changed in version 3.7: Deprecated.
Changed in version 2.8: The count() method now supports hint().

count_documents(filter, session=None, kwargs)

Count the number of documents in this collection.

The count_documents() method is supported in a transaction.

All optional parameters should be passed as keyword arguments to this method. Valid options include:

skip (int): The number of matching documents to skip before returning
results.
limit (int): The maximum number of documents to count.
maxTimeMS (int): The maximum amount of time to allow this operation
to run, in milliseconds.
collation (optional): An instance of <a
href="https://api.mongodb.com/python/current/api/pymongo/collation.html#pymongo.collation.Collation" rel="nofollow noreferrer">
Collation. This option is only supported on MongoDB 3.4 and
above.
hint (string or list of tuples): The index to use. Specify either the
index name as a string or the index specification as a list of tuples
(e.g. [(‘a’, pymongo.ASCENDING), (‘b’, pymongo.ASCENDING)]). This
option is only supported on MongoDB 3.6 and above.

The count_documents() method obeys the read_preference of this Collection.

Note When migrating from count() to count_documents() the following query operators must be replaced:

Operator     Replacement
$where        $expr
$near         $geoWithin with $center
$nearSphere $geoWithin with $centerSphere

$expr requires MongoDB 3.6+

For your further ref here and here


sql column names with single quotes I've a table the includes two columns ID and age. When view the table, the columns names are in single quotes. That's, 'ID', 'AGE'.
If I tried to do a simple select statement for ID:
SELECT ID FROM table1

I'll be getting an error message of "invalid identifier 'ID' "
What's the solution here?

I hope this is not the case, or you're going to get tons of problems down the line.
It's a Bad Idea to have any kind of "special" character in column names and single quotes would be just mind-bendingly difficult to work with.
Try this:
select `'ID'` from table1 ;

(That's Back-Tick, Single-Quote, capital-Eye, capital-Dee, Single-Quote, Back-Tick.)
If that does work (and I really, really hope it doesn't), then go find out who created this table and give them a damned good kicking!
If it doesn't work, then run this command and send us the output from it:
show create table table1 \g


How can I prevent oracle to create minidumps? For some reason, our application sometimes loses connections with the Databases, and then a LOT of minidumps are generated.

How can I track the reason for these minidumps? how can I enable something to prevent this to be created?

Reading those files there is only a picture of all windows events running. there's no useful information.

I'm new with oracle and i'm pretty sure there is  some button to disable it.

Across all websites I could see people upgrading clients, service packs and etc. Like this one:

https://superuser.com/questions/438315/process-keeps-creating-dump-files

EIDT: Even after upgrading the software and environment, I have a lot of minidumps.

Minidump files

The "minidump files" appear to be a Windows OS thing.

https://support.microsoft.com/en-us/help/307973/how-to-configure-system-failure-and-recovery-options-in-windows

But, if you disable them, you will just be hiding the fact that there is a problem.

I would prefer to hunt down the reason why they are being created and fix that problem.

The first thing to do is check which version you are running. For example, version 11.2.0.1 is known to be buggy.  If that is the case, your first step should be upgrading your database.

After that, I would look at the Oracle Alert logs and go from there.

note:  ORA-600/7554 usually means "Call Oracle Support".


Displaying Parent Child Information, With Certain Parent Columns Only Shown Once For a TSQL report, I have a one-to-many parent-child relationship.

I want to report both parent and child relationships in the same report, like so, with the parent columns on the left and child columns on the right:

ParentName | ParentNum | ParentIncome | ChildName | ChildNum | ChildAllowance
John       | 1         | 50000        | Johnny    | 1        | 5
Jane       | 2         | 55000        | Jackie    | 2        | 10
Jane       | 2         |              | Billy     | 3        | 5
Jane       | 2         |              | Sally     | 4        | 5
Jackie     | 3         | 90000        | Monique   | 5        | 0

I want to avoid duplicating certain columns, like ParentIncome, because users dump the report output into Microsoft Excel and I want to minimize the chances of them summing columns incorrectly.

Therefore, for each parent, I want to only display ParentIncome once (ParentNum and ParentName OK to repeat.). For rows other than the first row, display a blank cell (i.e., no NULL values).

Currently, I produce this report using a wrapper around a subquery. The wrapper looks like this for all the columns:

CASE WHEN DATA.ROW_NUMBER = 1
     THEN DATA.Field1
     ELSE '' END AS 'AColumn'

The subquery orders things using OVER and PARTITION BY and ORDER BY, so when ROW_NUMBER = 1, it's always a new parent.

It seems like this is something others have run into, though...in TSQL, can I get the same results in a simpler way, without the extra outer wrapper query to format things?

A general policy is to let the reporting layer handle things like only printing ParentIncome once.  However, since you are delivering a spreadsheet that will be used by others in who knows what manner, then I suppose you are stuck.

Because of the knowledge required you will need to develop some extra information (MIN, MAX, first, last, etc.) that is not known by a single row.  There are dodges different from ROW_NUMBER() OVER (PARTITION...), but there will still be an extra step.

See the following:

CREATE TABLE #parent
(ParentNum INT,
 ParentName VARCHAR(20),
 ParentIncome INT);

CREATE TABLE #child
(ChildNum INT,
 ChildParentNum INT,
 ChildName VARCHAR(20),
 ChildAllowance INT);

INSERT INTO #parent VALUES(10,'John',50000);
INSERT INTO #parent VALUES(20,'Jane',55000);
INSERT INTO #parent VALUES(30,'Jackie',90000);

INSERT INTO #child VALUES(1,10,'Johnny',5)
INSERT INTO #child VALUES(2,20,'Jackie',10)
INSERT INTO #child VALUES(3,20,'Billy',5)
INSERT INTO #child VALUES(4,20,'Sally',5)
INSERT INTO #child VALUES(5,30,'Monique',0)

-- Basic approach you may be using

See this example SQL Fiddle #1

SELECT pc.ParentName, pc.ParentNum,
    CASE WHEN pc.RowNum = 1 THEN CAST(pc.ParentIncome AS VARCHAR(10)) ELSE '' END as ParentIncome,
    pc.ChildName, pc.ChildNum, pc.ChildAllowance
FROM (SELECT p.ParentNum, p.ParentName, p.ParentIncome,
         c.ChildNum, c.ChildParentNum, c.ChildName, c.ChildAllowance,
         ROW_NUMBER() OVER (PARTITION BY ParentNum ORDER BY ParentNum) AS RowNum
       FROM #parent p JOIN #child c ON p.ParentNum = c.ChildParentNum) AS pc
ORDER BY pc.ParentNum, pc.ChildNum

    -- An alternative, but still using a subselect for one element

See this example: SQL Fiddle #2

SELECT  p.ParentName, p.ParentNum,
        CASE WHEN c.ChildNum = mc.MinChild THEN CAST (ParentIncome AS VARCHAR(10)) ELSE '' END AS ParentIncome,
        c.ChildName, c.ChildNum, c.ChildAllowance
       FROM #parent p
          JOIN #child as c
             ON p.ParentNum = c.ChildParentNum
          -- This subselect gives the MIN (or First) ChildNum per Parent
          JOIN (SELECT ChildParentNum, MIN(ChildNum) AS MinChild
                  FROM #child
                  GROUP BY ChildParentNum) AS mc
             ON mc.ChildParentNum = c.ChildParentNum
ORDER BY p.ParentNum, c.ChildNum

drop table #parent
drop table #child

Notice that I cast the ParentIncome as a VARCHAR(10) so that the datatype would be of the same type as the empty income of ''.  I originally used a NULL instead of a blank, but that might give you EXCEL problems.

Is it worth doing things this way?  It is up to you, but it primarily depends on what you like best.  The ROW_NUMBER() is a more powerful operator than MIN() and gives you more options, but in this case it appears that a MIN() will work for you.


Require JSON to have an element When working with the json datatype, is there a way to ensure the input JSON must have an element?

I don't mean primary, I want the JSON that gets inserted to at least have the id, name element. It can have more but at the minimum those two must be there. Can this be done?

You can do that with a column check:

ALTER TABLE table ADD
  CHECK ((column->'id') is not null and (column->'name') is not null);


MySQL user with empty hostname On a MySQL 5.6 server, the mysql.user table contains an user with an empty hostname ('jdoe'@''). What does that mean?

User whose name is joe can connect from any host.

Please note what MySQL 5.0 Certification Study Guide

say in its bulletpoints on Page 498 Paragraph 6:

  On Unix, MySQL comes with a mysql_secure_installation script that can
  perform several helpful security-related operations on your
  installation. The script has the following capabilities:

  Set a password for the root accounts
  Remove any remotely accessible root accounts.
  Remove the anonymous user accounts. This improves security because
  it prevents the possibility of anyone connecting to the MySQL server
  as root from a remote host. The results is that anyone who wants to
  connect as root must first be able to log in on the server host, which
  provides an additional barrier against attack.
  Remove the test database (If you remove the anonymous accounts, you
  might also want to remove the test database to which they have
  access).

That user needs to be removed immediately.

Simply run

DELETE FROM mysql.user WHERE host='';
FLUSH PRIVILEGES;

GIVE IT A TRY !!!

UPDATE 2017-02-01 17:07 EST

According to the MySQL Documentation on Access Control, Stage 2: Request Verification

  A '%' or blank Host value means “any host.”

This is applicable at all levels of grants.


Pentaho Data Integration - Kettle- Update Identity Column in Microsoft SQL Server For migration purposes I need to migrate existing ID's from my old database to my new table in MS SQL Server.

I can truncate my table in MS SQL Server and then adjust my database connection in Pentaho to 'SET IDENTITY_INSERT tablename ON'. Then I am able to insert my ID's through a table output step, but I want to be able to UPDATE my existing ID without truncating my table in MS SQL Server.

Could this be possible using Pentaho?

SQL Server does not allow updates the IDENTITY column value regardless of application technology.

One way to perform such a migration is to switch data into a table with identical schema (except for the IDENTITY property), perform the update, and then SWITCH back into the main table.

CREATE TABLE dbo.TableWithIdentity(
      IdentityColumn int IDENTITY NOT NULL
        CONSTRAINT PK_TableWithIdentity PRIMARY KEY
    , OtherData varchar(20) NULL
    );
CREATE INDEX idx_TableWithIdentity_OtherData
    ON dbo.TableWithIdentity(OtherData);
INSERT INTO dbo.TableWithIdentity VALUES
      ('Sample data one')
    , ('Sample data two')
    , ('Sample data three');
GO

SET XACT_ABORT ON;
BEGIN TRY
    BEGIN TRAN;
    --create staging table with same schema, indexes, and constraints but without IDENTITY
    CREATE TABLE dbo.TableWithoutIdentity(
          IdentityColumn int NOT NULL
            CONSTRAINT PK_TableWithoutIdentity PRIMARY KEY
        , OtherData varchar(20) NULL
        );
    CREATE INDEX idx_TableWithoutIdentity_OtherData
        ON dbo.TableWithoutIdentity(OtherData);

    --SWITCH data into staging table
    ALTER TABLE dbo.TableWithIdentity
        SWITCH TO dbo.TableWithoutIdentity;

    --example update to change identity value
    UPDATE TableWithoutIdentity
    SET IdentityColumn = IdentityColumn + 100;

    --SWITCH data back into main table
    ALTER TABLE dbo.TableWithoutIdentity
        SWITCH TO dbo.TableWithIdentity;

    DBCC CHECKIDENT(N'dbo.TableWithIdentity');

    DROP TABLE dbo.TableWithoutIdentity;

    COMMIT;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    THROW;
END CATCH;
GO


Key column 'FK_UserQuestion' doesn't exist in table error I used the following syntax and keep getting the following error at the end. " Key column 'FK_UserQuestion' doesn't exist in table":

CREATE DATABASE QA;

CREATE TABLE QA.Users
(
UserID int AUTO_INCREMENT PRIMARY KEY,
fName varchar(150),
lname varchar(150),
isadmin bit
) engine = INNODB;

CREATE TABLE QA.Question
(
QuestionID int AUTO_INCREMENT PRIMARY KEY,
UserID int,
Question varchar(65535),
FOREIGN KEY (FK_UserQuestion, UserID)
    REFERENCES QA.Users (UserID)
) engine = INNODB;

CREATE TABLE QA.Answer
(
AnswerID int AUTO_INCREMENT PRIMARY KEY,
QuestionID int,
UserID int,
Answer BLOB(65535),
IsSolver bit,
FOREIGN KEY (User_ID, UserID)
    REFERENCES QA.Users(UserID)
FOREIGN KEY ( FK_AnswerQuestion,QuestionID)
    REFERENCES QA.Question (QuestionID)
) engine = INNODB;

I've tried to put out the "QA." from each table, and tried to run statements that are separated by semi-colon, each turn too, but nothing solved. Tried many things and nothing I did is correct for MySQL Workbench 6.3 CE.

Your foreign keys have the wrong format. The syntax should be:

FOREIGN KEY <name> (column_1, ..., column_n)
REFERENCES <parent table> (column_1, ..., columns_n)

In your case:

FOREIGN KEY FK_UserQuestion (UserID)
REFERENCES QA.Users (UserID)

I.e.

CREATE TABLE QA.Question
( QuestionID int AUTO_INCREMENT PRIMARY KEY
, UserID int
, Question varchar(65535)
, FOREIGN KEY FK_UserQuestion (UserID)
  REFERENCES QA.Users (UserID)
) engine = INNODB;

A similar change for QA.Answer should also be made


Is restoring a mysql database backup faster at removing white space than running OPTIMIZE? I have a myisam database running on MariaDB 10.x.  I'm pretty sure it has a lot of whitespace that I'd like to trim down.  I was planning to run OPTIMIZE <TABLE NAME>; but a thought occurred to me: would it be faster/easier to just collect a backup and restore it?

For example...

1) Run: mysqldump -u root --hex-blob --add-drop-database --databases FooDatabase > /tno/FooBackup.sql

2) Then run: mysql -u root -vvv < /tmp/FooBackup.sql

The white space wouldn't be in the backup file so presumably it would be compact upon restoring... and that way, MariaDB wouldn't need to go row by row checking for stuff.

Is that a bad idea?  Admittedly, the backup/restore wouldn't check for errors or do the other things that optimize does but if I'm ONLY interested in eliminating white space, wouldn't it be faster?

Both will achieve essentially the same thing. If your exact db version doesn't offer online OPTIMIZE then the dump and restore could be useful if you restore into a new table and then DROP the old one and RENAME the new one, allowing concurrent selects during the import. Regarding the speed, the dump and restore might be a bit faster but it's probably not a huge gain. I prefer it if I'm working with a version that doesn't support online OPTIMIZE.

As Rick James mentioned this sort of thing isn't often necessary, but I have seen it help to bulk re-balance indices on tables with columns struggling to keep up with a large number of concurrent updates.


Streaming Replication on Two Postgres Systems? There's two servers, a Primary Server and a Backup Server. Both have Postgres installed. The Primary Server has one database, and the Backup Server has two - one for a warm backup/standby of the Primary Server's database, and another for Training and whatnot.

I need a way to maintain a warm standby on a single database, the Backup Database, without using some sort of log-shipping method that will overwrite all the data on the Training Database.

I'm thinking about trying to use Streaming Replication. What I need to know is if I will be able to keep up a warm backup of the Primary Database on one database, without overwriting the Training Database on the machine.

Streaming Replication will only replicate your entire cluster, and not a single database. It will also make the slave database read only, so you can't use it as your "Training Database".

If you need to do that, you need to look at something like Slony, Bucardo or Londiste.

However, those are much more complex to set up and maintain. Another option is to run two completely different clusters ("instances") on the slave - one that is the slave and one that is the training. They'll then run on different ports, with different memory pools, etc, etc. It's not great for performance to do that, but if it's just a training database that may not matter, and it's a much easier replication system to work with.


ReportServerTempDB in AlwaysOn Availability group ReportserverTempdb logfile is 6TB in size and growing and I need help. I am supporting a huge reporting server farm and the client wants reporting services to be in AlawysON Availability group(AAG) and we configured reportserver and reportserverTempDB on in three node Primary replica, synchronize Secondary replica and DR in a different location with manual synchronization.
Microsoft recommends reportservertempdb to be in simple recovery mode but if you add it to AAG that is not an option. Does anyone has reportservertempdb in AAG?
I would like to advise customer to take reporservertempDB out of AAG and we can just keep the copy of the reportservertempdb on each node and that should be fine.
Please advise.

Microsoft describes it's support for Availability Groups with SSRS in BOL. In particular is the section titled "Report Server Databases and Availability Groups."  In my experience, having ReportServer and ReportServerTempDb be part of an AG works fine in most cases, but there is some peculiar behavior with subscriptions and failover--which is described in the BOL article.

Having independent ReportServerTempDb databases on each server, but not participating in the AG is not something I would try--but it would likely be problematic in it's own ways. It would also not be a supported configuration.

You should look at this answer to see why your transaction log is growing. I suspect it is due to an open transaction in the ReportServerTempDb. On a number of occasions, I have seen the CleanBatch process get hung, and create a scenario where it has an open transaction but is performing no work. The net result is that your transaction log gets huge. If you see the same behavior, then simply kill the spid with the open transaction, and things should return to normal.