Using sql server alias via management studio on another computer Suppose I setup alias in sql configuration manager, then on the same server, via management studio I can use the alias name to connect to the server.
However, on another computer when I open management studio, then can I use the alias name to connect? My understanding is that the SQL server IP or server name can be used as that is recorded in the DNS. Can alias work and if yes, then how?

No, aliases are local to the client machine. Use DNS if you want a central configuration place.


SQL Server 2008 R2 - user disk quota Is it possible to apply restrictions on how much data a user / login may store in a database? I found no way to apply such restrictions to schemas, for instance.

If there is no built-in mechanism then I'm curious if someone had implemented some kind of it.

Seems a bit ridiculous, I know but we have some users and want them to restrict writing too much data into WorkDB so that their activities (queries, inserts) wouldn't eat out all the resources.

Resource Governor can only maintain CPU/RAM.

Environment: SQL Server 2008 R2 Enterprise edition.

There is one option as I know: one user - one database. Set Initial Size and disable Autogrowth (database options).


Stored procedure takes 18 seconds on first load, 0 seconds on subsequent loads, waiting several minutes makes it slow again I have a stored procedure that returns a lot of data for a business dashboard. On first load it takes 18 seconds, after that it takes 0 seconds, even if I change the input parameters at random it stays at 0 seconds and returns the correct results.

If I wait about three minutes it goes back to 18 seconds. This is not acceptable for the user and I have exhausted every option I know of.

Why would it be slow on the first load but instant after? I understand it may be recompiling the execution plan so on each query I added  OPTION (KEEP PLAN); with no effect. My stored procedure does not have 'with recompile'. I downloaded 'datawizard sql profiler' and set up a trace to show recompiles but it does not show any, even when I have 'with recompile' in the query.

My only other thought is that mssql is doing some kind of result caching, but the query runs fast even when I change the parameters.

Why would it take 18 seconds, then 0, then 18 seconds again after I wait for a while?

The issue was the setting "Auto close" was set to true on a series of databases on the server. Even though the databases were unrelated their "auto close" setting was flushing all execution plans on the server.

Here are the types of log issues you will see with auto_close set to true:

SQL Server has encountered 24 occurrence(s) of     cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
SQL Server has encountered 24 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
SQL Server has encountered 24 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

As you can see in this article, NEVER SET AUTO CLOSE TO TRUE!!! http://sqlmag.com/blog/worst-practice-allowing-autoclose-sql-server-databases


What's better/faster? MySql or FileSystem? Let's imagine a web site that is a directory of people. For each person there may be a profile photo and a biography.

I'll admit my SQL queries could be better but in general what would be faster and use less processing power.

To check if a file exists and then open it
 or

check against MySql to see if a bio exists and display it.

I'm pretty sure in the above case the filesystem will smoke the mysql database.

What if I make the database a read only delimited txt file?

What's faster in this case?

Is there a certain point where if the txt file has too many records it's better to use MySql?

The file system is useful if you are looking for a particular file, as operating systems maintain a sort of index. However, the contents of a txt file won't be indexed, which is one of the main advantages of a database. Another is understanding the relational model, so that data doesn't need to be repeated over and over. Another is understanding types. If you have a txt file, you'll need to parse numbers, dates, etc.

So - the file system might work for you in some cases, but certainly not all.


In Oracle, why would I not always delete all old archive log files with RMAN? backup archivelog all delete all input;

I do not understand why you would not always do this.  Why would I only want to delete archive log files from one location?

When Oracle saves the archive log files to multiple destinations, am I correct they are all duplicate and exactly the same in each location (and thus why the all vs not all)?

Yes, archive log can duplicated in multiple locations, and in many cases delete all is what you normally do.

But sometimes you may want to control one or more location without rman. For instance, Data Guard is not available in Standard Edition.   However, couple simple shell scripts can replace it to some extent . Surely, it's not a full substitution, but it's enough for most small-to-medium systems that use Oracle DB .  All you need is to reliably copy archive logs from one host to another, and restore them.  It's much better to give full control over one of archive log destinations to these scripts (or maybe homebrew applications or third party products) , so they can decide when it's safe to delete them.


Oracle Linked Server query error We have a problem with a query from a SQL Server 2008R2 - 64bit
to an Oracle Linked Server.

We use the Oracle 64-bit driver for Windows.

When we query a certain table we get the following error:

  Msg 7347, Level 16, State 1, Line 1 OLE DB provider 'OraOLEDB.Oracle'
  for linked server 'SERV_VISION2' returned data that does not match
  expected data length for column
  '[SERV_VISION2]..[VISION].[INV_M001].CUS_NM'. The (maximum) expected
  data length is 30, while the returned data length is 24.

The problem seems to be the data in the CUS_NM column.
In the rows where he gives this error the data is like this:

¿¿¿

So like a question mark but upside down. There doesn't seem to be a problem with the
length of the column, for one reason he can't handle this data I guess.

Did anyone experience this problem?
Anyone a clue how to solve this?

That's most likely an NLS (language) problem and you've got customer names with characters your database cannot handle. You'll need to determine the actual type of the CUS_NM column in the Oracle database.


INSERT using results of CTE INSERT to provide unique id values I am writing a job to transform data from an old design into a new design. In this process, I need to take the id from an insert into a separate table and use that in an insert to the target table, as such:

CREATE TABLE t1 {
  t1_id BIGSERIAL,
  col1 VARCHAR
};
CREATE TABLE t2 {
  t2_id BIGSERIAL,
  col2 VARCHAR, -- renamed from col1 to avoid confusion
  t1_id BIGINT REFERENCES t1.t1_id
};

I have the SQL defined that matches the following form:

WITH ins AS (
  INSERT INTO t1 (t1_id) VALUES (DEFAULT) RETURNING t1_id
) INSERT INTO t2
  (col1, t1_id)
SELECT
  a.val1, (SELECT * FROM ins)
FROM t3 a;

I wanted this to run the SELECT * FROM ins for every row of the SELECT .. but instead it only runs it once and uses that value for all rows in the SELECT.  How can I restructure my SQL to get the desired behavior?

edit4

t1 ends up looking like:

1,<NULL>
(1 row)

t2 ends up looking like:

10,'a',1
11,'b',1 -- problem with id from t1 being 1
12,'c',1 -- problem with id from t1 being 1
.
.

What I want t1 to look like:

1,<NULL>
2,<NULL>
3,<NULL>
.
.

What I want t2 to look like:

10,'a',1
11,'b',2 -- id from t1 of 2
12,'c',3 -- id from t1 of 3
.
.

edit
To address what a_horse_with_no_name said, I also tried this (with the same result):

WITH ins AS (
  INSERT INTO t1 (t1_id) VALUES (DEFAULT) RETURNING t1_id
) INSERT INTO t2
  (col1, t1_id)
SELECT
  a.val1, b.t1_id
FROM t3 a
JOIN ins b ON TRUE;

edit2
I just tried directly referencing the appropriate SEQUENCE in my query, and that DOES work - but I don't like that solution very much at all (mostly because I don't like hard-coding object names.)  If there is ANY solution other than directly referencing the name of the SEQUENCE I would appreciate it. :)

edit3
I suppose another solution would be to make use of a PROCEDURE to do the INSERT instead of a CTE .. but I'd still appreciation options/suggestions.

I don't understand why you need 2 tables if they have only 1-1 relationship. But here it is (pk is the primary key of t3):

WITH ins AS (
  INSERT INTO t1 (col1)
    SELECT NULL FROM t3
  RETURNING t1_id
)
, r AS
( SELECT t1_id, ROW_NUMBER() OVER () AS rn
  FROM ins
)
, t AS
( SELECT *, ROW_NUMBER() OVER () AS rn
  FROM t3
)
INSERT INTO t2
  (col1, t1_id)
SELECT
  t.val1, r.t1_id
FROM t
  JOIN r USING (rn) ;

If your t3 is the results of a SELECT instead of a preexisting table, you can implement it as such so that you don't have to repeat the t3 query twice:

WITH t3 AS (
  SELECT ...
), ins AS (
  INSERT INTO t1 (col1)
    SELECT NULL FROM t3
  RETURNING t1_id
), r AS (
  SELECT t1_id, ROW_NUMBER() OVER () AS rn
  FROM ins
), t AS (
  SELECT *, ROW_NUMBER() OVER () AS rn
  FROM t3
) INSERT INTO t2
  (col1, t1_id)
SELECT
  t.val1, r.t1_id
FROM t
  JOIN r USING (rn);


Oracle Tables Visible to Other Users I have an Oracle database that has one user that has access to everything. This was fine when we only had one user working on the database. But now that we have more users we want to tighten up security and have different users.

I created each user, gave them DBA access, and they are part of the same tablespace. When the user logs in, there are no tables visible to the users. I've tried to GRANT SELECT to a test table and still nothing. I tired to set up a role for the user and still nothing. Am I missing something or if the user is not the owner of the table other users cannot see the tables or run queries?

It's hard to guess what's going on on your system without code snipets to evaluate, but it seems you're not using the fully qualified table name [schema name].[table name]. If you don't want to use the fully qualified table name, you may create PUBLIC SYNONYMS. Ex from Oracle documentation:

CREATE PUBLIC SYNONYM offices
   FOR hr.offices;

Having said that, if you are granting DBA privileges to all users, you're not tightening up security at all! If you only want them to query some tables, grant them CONNECT and SELECT privileges. If you're felling really lazy, grant them SELECT ANY TABLE privilege, but don't grant them DBA! DBA privileges give users a lot more power than to just query tables. They'll be able to change system parameters, drop or alter any objects etc etc.


mysql bin-log.index not found I moved my bin-logs to a different folder:

log-bin = /storage/mysql/bin-log
log-bin-index = /storage/mysql/bin-log.index

When I try to start mysqld I get this:

/usr/libexec/mysqld: File '/storage/mysql/bin-log.index' not found (Errcode: 13)

Permissions for the files and directory are fine, sudo -u mysql touch /storage/mysql/bin-log.index is working fine and there is no apparmor installed.

Any ideas what could cause this?

SELinux was installed and it didn't allow access to those files.

Disabling it solves the problem.


Applying Multiple Window Functions in 1 SELECT using different partition I was wondering if there is anything wrong with using this method.

I have a working demo of what i'm trying to do below

In the SELECT statement I have used 2 OVER clauses.

Is there any risk that the data could get out of sequence?

CREATE TABLE basic_pays (
    employee_id int,
    fiscal_year INT,
    salary DECIMAL(10 , 2 ),
    PRIMARY KEY (employee_id, fiscal_year)
);

INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(100,2017,24000);
INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(101,2017,17000);
INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(102,2017,18000);
INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(103,2017,9000);
INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(100,2018,25920);
INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(101,2018,18190);
INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(102,2018,18360);
INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(100,2020,26179.2);
INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(101,2020,19463.3);
INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(102,2020,19278);
INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(103,2020,10206);

SELECT
    employee_id,
    fiscal_year,
    salary,
    LAG(salary)
        OVER (PARTITION BY employee_id ORDER BY fiscal_year) previous_salary,
    SUM(salary) /SUM(SUM(salary))
        OVER (PARTITION BY fiscal_year ORDER BY fiscal_year) salary_percentage
FROM
    basic_pays
GROUP BY
    employee_id, fiscal_year, salary
ORDER BY fiscal_year, employee_id

DROP TABLE basic_pays

That should be fine.

Your first column (LAG(salary)) will be calculated over the partition you specified, which the second column will be calculated over the second partition.

Note that each partition specifies its own ORDER BY clause - so your windowed functions are calculated based on your row's position within the partition and its own order by clause.

I can't see any issues with this.

When you run it, do you get the results you expect?


max(max(col1), max(col2), ...) does not work I have a table with three integer columns:

select max(col1), max(col2), max(col3) from mytable

I want the max of both three columns. But this does not work:

select max(max(col1), max(col2), max(col3)) from mytable

How to get this done?

I use PostgreSQL, but I hope that this can be solved with standard SQL.

That should be GREATEST(), not MAX(). MAX() is for aggregating over values from rows, GREATEST() is for aggregating over a list of values (from columns, expressions).

You can either find the 3 max values, one for each column and then the greatest of the three:

select greatest(max(col1), max(col2), max(col3))
from mytable ;

or find the greatest value in each row and then the max over all of them:

select max(greatest(col1, col2, col3))
from mytable ;


PostgreSQL database locks One of our CRM applications has a PostgreSQL database. Their db guys created 10 views that queries this production PostgreSQL database

We plan to do a SELECT query via a PHP script on each of these views at 06:15 every morning and it takes around 1 hour to execute. Part of this process is also to save the query result into a CSV
06:15 is pretty close to when the users actually start using this CRM application and pretty sure these queries on the views will at times overlap on CRM application usage by users
Does SELECT queries on views on PostgreSQL databases lock the tables and in turn then cause any issues on the application?
And also the other way around if a table is locked due to application usage will this cause any issues in terms of querying the view? Or will it just skip that row?
I suppose it would also be best practice to monitor server load during this time? simply use TOP command on linux instance?
Any other concerns?

A query that only reads data will not block normal user activity. Readers don't block writers and vice versa.
However, it may consume substantial resouces on the database server, which could impair performance for the users.


Limits of testing HA with a single server replica set? I'm teaching myself MongoDB HA, going through various replica set scenarios, setting up priorities, arbiters, hidden replica sets, etc.  Currently I'm using multiple mongod's on a single Windows server, running on separate ports obviously.

Are there specific scenarios that I wouldn't able to simulate with things like rs.stepDown() and rs.freeze()?  Or by stopping services?

You can certainly test standard replica configuration and deployment issues on a single server, but some scenarios are easier to set up with multiple environments (even if those happen to be VMs or containers on the same host server). The rs.freeze() and rs.stepDown() commands help control election outcomes; for HA testing I expect you'd be randomly shutting down or firewalling mongod processes to confirm the outcome is as you anticipated.

  Are there specific scenarios that I wouldn't able to simulate with things like rs.stepDown() and rs.freeze()? Or by stopping services?

It depends how thoroughly you are trying to test. VMs definitely provide more flexibility -- for example, if you want to test possible effects of clock skew or resource limitations.

To simulate more real-world deployments you should also consider network simulation using a tool like mongodb-bridge. There are common network issues like latency (particularly for geographically distributed deployments) as well as more insidious problems like asymmetric routing or connectivity.

If you are considering HA from the application point of view, you should test using an application/driver with an appropriate write concern. For example, you may want to ensure that an application using a write concern of w:majority can still get acknowledged writes in the event of node failure. With a Primary/Secondary/Secondary replica set any single node can fail and there will still be two data-bearing nodes to satisfy w:majority; if you have a Primary/Secondary/Arbiter replica set, the failure of a single data-bearing node will still allow a primary to be elected, but w:majority write concern cannot be satisfied.

If you want some ideas on replication scenarios to test, an interesting starting point might be the JavaScript test suite used in the MongoDB code base: https://github.com/mongodb/mongo/tree/master/jstests/replsets.


how to give GLOBAL PRIVILEGES in MySQL? I need limited Global permission like Select Execute and grant privileges  to one user.

how can i give, can any one help me.

To provide All privileges to an user from a specific server your Grant commands can be like the one below.

     GRANT ALL PRIVILEGES ON database_name TO 'user'@'hostname' IDENTIFIED BY PASSWORD <password>;
     FLUSH PRIVILEGES;

To provide select and execute grants to your user,

GRANT SELECT, EXECUTE ON database_name TO username@'localhost' IDENTIFIED BY 'password';
FLUSH PRIVILEGES

Don't forget to FLUSH PRIVILEGES after executing the grants.


Same restore file (.bak), huge difference of execution duration time on various computers First of all, the question is nothing to do with SQL performance optimization.

I have a .BAK SQL Server backup, and have restored it on my computer.

My computer: i7 2600 3.4GHz, 240GB SSD, 8GB RAM, Win 7 Ultimate x64, SQL Server 08 Ent x64.

When I ran a particular stored procedure the execution time is about 22 ~ 28 sec.

I restored the same .BAK file to my several of my colleague's computers, and run the same stored procedure, execution time is about 2 ~ 4 seconds.

*My colleague's computers: Various Intel/AMD CPUs, SATA HDD, Windows XP or Server 2003 or Windows 7 (x86/x64), with SQL Server 2005 or 2008; one colleague even has the same OS and SQL Server as mine.

I also have a server, XEON 5600 CPU, Windows Server 2003, SQL Server 2005, restore and run in about 46 seconds.

I've tried every method I could think of however I have no idea why my computer is running the stored procedure so slowly.

Why would my computer, when spec-wise it is so much faster, perform so much slower?

Is Instant File Initialisation on on your colleagues computer?

Basically, this hugely decreased the time to initialise the MDF file. Without it, the entire file needs written with zeros. The log (LDF) file is always zero-initialized.


Using inner join and left join together I have about 35 tables. I want to use them in the same query. I do not know how it possible.
I have three tables:
land

lid
name
meter
address
picid

owner

ownerid
name
family
mobile
lid

Every land can have a maximum of 12 owners.
pic

picid
path
picinfo

The result that I need:

lid, name, meter, address, path, picinfo, owner_name_1, owner_family_1,
owner_mobile_1, owner_name_2, owner_family_2, owner_mobile_2, ....
owner_name_12, owner_family_12, owner_mobile_12

Tables

DECLARE @Land AS table
(
    [lid] integer PRIMARY KEY,
    [name] varchar(50) NOT NULL UNIQUE,
    [meter] integer NULL,
    [address] integer NULL,
    [picid] integer NULL
);

DECLARE @Owner AS table
(
    [ownerid] integer PRIMARY KEY,
    [name] varchar(50) NOT NULL UNIQUE,
    [family] varchar(30) NOT NULL,
    [mobile] varchar(30) NULL,
    [lid] integer NOT NULL
);

DECLARE @Pic AS table
(
    [picid] integer PRIMARY KEY,
    [path] varchar(256) NOT NULL,
    [picinfo] varchar(100) NULL
);

Sample data

INSERT @Land
    ([lid], [name], [meter], [address], [picid])
VALUES
    (1, 'land 1', 1, 1, 1),
    (2, 'land 2', 2, 2, 2);

INSERT @Owner
    ([ownerid], [name], [family], [mobile], [lid])
VALUES
    (1, 'owner 1 of land 1', 'family 1', 'mobile 1', 1),
    (2, 'owner 2 of land 1', 'family 2', 'mobile 2', 1),
    (3, 'owner 3 of land 1', 'family 3', 'mobile 3', 1),
    (4, 'owner 1 of land 2', 'family 4', 'mobile 4', 2),
    (5, 'owner 2 of land 2', 'family 5', 'mobile 5', 2);

INSERT @Pic
    ([picid], [path], [picinfo])
VALUES
    (1, 'Path 1', 'Pic Info 1'),
    (2, 'Path 2', 'Pic Info 2');

Solution

WITH Numbered AS
(
    -- Join the rows and number land owners
    SELECT
        L.[lid],
        L.[name],
        L.[meter],
        L.[address],
        owner_name = O.[name],
        owner_family = O.[family],
        owner_mobile= O.[mobile],
        P.[path],
        P.[picinfo],
        rn = ROW_NUMBER() OVER (
            PARTITION BY L.lid
            ORDER BY O.ownerid)
    FROM @Land AS L
    JOIN @Owner AS O
        ON O.lid = L.lid
    JOIN @Pic AS P
        ON P.picid = L.picid
)
SELECT
    N.[lid],
    N.[name],
    N.[meter],
    N.[address],
    N.[path],
    N.picinfo,
    [owner_name_1] = MAX(CASE WHEN N.rn = 1 THEN N.owner_name ELSE NULL END),
    [owner_family_1] = MAX(CASE WHEN N.rn = 1 THEN N.owner_family ELSE NULL END),
    [owner_mobile_1] = MAX(CASE WHEN N.rn = 1 THEN N.owner_mobile ELSE NULL END),
    [owner_name_2] = MAX(CASE WHEN N.rn = 2 THEN N.owner_name ELSE NULL END),
    [owner_family_2] = MAX(CASE WHEN N.rn = 2 THEN N.owner_family ELSE NULL END),
    [owner_mobile_2] = MAX(CASE WHEN N.rn = 2 THEN N.owner_mobile ELSE NULL END),
    [owner_name_3] = MAX(CASE WHEN N.rn = 3 THEN N.owner_name ELSE NULL END),
    [owner_family_3] = MAX(CASE WHEN N.rn = 3 THEN N.owner_family ELSE NULL END),
    [owner_mobile_3] = MAX(CASE WHEN N.rn = 3 THEN N.owner_mobile ELSE NULL END)
    -- Repeat the pattern up to 12
FROM Numbered AS N
GROUP BY
    N.[lid], -- key column
    N.[name],
    N.[meter],
    N.[address],
    N.[path],
    N.[picinfo]
ORDER BY
    N.lid;

Output

Execution plan

Solution description

The basic idea is that joining the three tables is easy. You don't even need an outer join at all if there is no land without an owner. The problem is that this produces rows, where you want columns in your output, with the columns numbered by owner. The numbering part is easy, using ROW_NUMBER.

Turning rows into columns is known as a pivot, the expanded form of which uses MAX, CASE, and GROUP BY as shown in the above code. You just need to repeat the MAX(CASE... sections to accommodate twelve possible owners.

There are a number of good questions and answers tagged pivot on the site already that you might like to look at. One relevant to your current problem is Need help with SQL Server PIVOT.


Index on integer and string fields A table has about 300M records. Each has an unique ID that starts with 13 to 14 digits (with the first digit on the left being either 1 or 2), and is optionally followed by a string of up to 7 ASCII chars. Two examples are 1450123410123 and 21347854123024Asd_uhA.

Currently, I'm storing this field as a VARCHAR with a PRIMARY KEY, and am worried about the memory usage of the index. I'm thinking of splitting this field into two columns, a BIGINT and a VARCHAR. Will the PRIMARY KEY created as a combination of these two columns consume less memory?

Update: Here are the two possibilities.

Please note that I care about inserts and updates. I will have only one huge select to export the data after a long time.

CREATE TABLE without_split (
  id varchar(21) CHARSET=ascii COLLATE ascii_bin NOT NULL,
  content text NOT NULL
  PRIMARY KEY id
);

CREATE TABLE with_split (
  id_prefix bigint unsigned NOT NULL,
  id_suffix varchar(7) CHARSET=ascii COLLATE ascii_bin NOT NULL,
  content text NOT NULL
  PRIMARY KEY (id_prefix, id_suffix)
);

MyISAM! Shame.  Switch to InnoDB.

Don't worry about that PRIMARY KEY; it is too much hassle to split it up, etc.  But do make sure it is the 'appropriate' character set:

VARCHAR(21) CHARACTER SET ascii

If it is case sensitive, then add on COLLATE ascii_bin.

Yes, there is a memory and disk penalty.  But there are tradeoffs with speed, simplicity, etc.

(If it were 100 characters long, I might give a different answer.)

Numbers for MyISAM

In MyISAM, assuming the strings are half length on average:
PRIMARY KEY(decimal_14_0, varchar_7) needs 2*(7+(1+3.5)) bytes.
PRIMARY KEY(varchar_21) needs 2*(1+(13.5+3.5))

2* because PK fields stored with data and with index;
7 bytes for DECIMAL(14,0);
1=length field for VARCHAR;
3.5(?)=avg bytes for up-to-7-char suffix;
13.5(?) for avg # digits in prefix.

After cancelling out things, the diff is 2*(13.5-7)=13 bytes smaller per row (data+pk) for DECIMAL+VARCHAR. 300M*13=4GB.

(InnoDB computation is in Comments.)


How do I run a database heavy query over jdbc without timing out (Oracle)? For one of our applications (Pentaho Data Integration), we need to run queries through the oracle jdbc. Some of these queries are computation expensive and can take an hour or more to return any rows to the client. When I try to run one of the long running queries with default jdbc parameters, I inevitably get: "IO Error: Socket read timed out" after ~30 minutes even though the database is still processing the query (verified in V$SESSION and V$SQLAREA). I am using the newest ojdbc8.jar direct from Oracle and running Oracle Enterprise 12.1.0.2.0.

EDIT:
The output of these queries is being stored in a separate database. The nature of our environment is such that we have an arbitrary number of queries that could be created/run so using stored procedures would be cumbersome.

You can use PL/SQL and schedule jobs to populate tables or schedule jobs to update materialized views. Then once the data has been updated you can query as much as you want, since the heavy lifting will be done. You should also try tuning the queries. If you have an index with the filter columns and any other columns that you need, then you can avoid reading the index then the table. If you start indexes with the low cardinality columns going towards high cardinality columns, you can reduce the amount of rows looked at. Look at materialized views and including rowid so that you can try to do a fast refresh.


Cannot connect database as sys as sysdba using sqlplus I am new to oracle server 18c (currently taking online course). My problem is sql*plus where I tried to use sqlplus then sys as sysdba (it did not work) and the error appeared below.

However, if I use sqlplus / as sysdba (it worked, and I don't know why)

I have tried this command: lsnrctl start, but again not working. In addition, I have checked my listener.ora and tnsnames.oraca

I have attached my environment set up in .bash_profile

Please help me this. Thank you

If the password contains special characters such as @, enter the password between double quotes:

$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Nov 11 15:41:40 2019
Version 19.5.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

SQL> connect sys/a@abcd as sysdba
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Warning: You are no longer connected to ORACLE.
SQL> connect sys/"a@abcd" as sysdba
Connected.
SQL>


PostgreSQL update table then insert on another table I have a User table and a Log table. I need to update a field on the User table and insert a new entry on the Log table recording the update. I have written the statement for the update:
  UPDATE users SET free_shipping_until = spu.created_at::date + interval '1 year'
  FROM shipping_priority_users AS spu
  WHERE spu.role_id = #{role.id} and users.id = spu.user_id and spu.created_at is not null;

For each update I need to also add (probably in a transaction) an insert statement on the Log table which has the following columns
user_id: string,
created_at: timestamp
data: jsonb

The data column contains a jsonb value including the free_shipping_until value from the update.

data: {"free_shipping_until": [null, "2021-07-30"]}

user_id should match the updated record's value
The created_at column is the current time, I'm using RoR and could interpolate the value with the expected format.

I'm using PostgreSQL 12.3

You can use a data modifying CTE to do this in a single statement:
with changed_user as (
  UPDATE users
    SET free_shipping_until = spu.created_at::date + interval '1 year'
  FROM shipping_priority_users AS spu
  WHERE spu.role_id = #{role.id}
    and users.id = spu.user_id
    and spu.created_at is not null
  returning *
)
insert into log (user_id, created_at, data)
select id,
       current_timestamp,
       jsonb_build_object('free_shipping_until', array[null, free_shipping_until::text])
from changed_user;

from changed_user


Memory Grant Warning on what appears to be a better query? OK, I was looking at potentially reducing this (as more of an exercise than a need mind you) and it seems that Im getting this warning on what appears to be a better performing query and not on the query that requires more memory to execute? Is this weird or am I just over thinking?

Anyone want to educate me here (sorry for the large images)?

1st query and hover over:

    UPDATE c SET c.scust_phone = cc.scontract_holder_hphone
    FROM #Claims c
    INNER JOIN SCS.scs_auto_sra.scs_contracts cc ON c.icontract_id = cc.icontract_id ;

second query and hover over:

    UPDATE c SET c.scust_phone = cc.scontract_holder_hphone
    FROM #Claims c
    INNER JOIN
        (
            SELECT icontract_id, scontract_holder_hphone
            FROM SCS.scs_auto_sra.scs_contracts cc
            WHERE EXISTS (SELECT icontract_id FROM #Claims x WHERE x.icontract_id = cc.icontract_id )
        ) cc ON c.icontract_id = cc.icontract_id ;

Actually, the warning for excessive memory grant uses an hardcoded logic. It raise an alert when a certain % of the allocated memory is not use but it does not considere the amount of wasted memory.

It cannot be "trusted" and you should not worry about it if, like in this case, it's only 1Mb that is allocated and not use.

I've seen cases where I got a warning for something like this (1Mb allocated, 1 Mb non-used) and cases where there was 256Mo allocated and like 136 Mo used (so 120Mo non-used) and there was no warning.


Trigger in combination with transaction Suppose we have the following situation:

We have a table (let's say Table_A), wich has a trigger on INSERT. The trigger job is to update some rows in table_B based on the inserted values in table_A.

Now, everything is ok when we simple insert a row in the table, but what about situations where we insert data through a transaction?. Will the trigger wait until all transactions statements run successfully, or it will be fired the moment it recognizes the insert?. If the trigger fires immediately when it recognizes the first insert, what will happen if the transaction fail on the last row?. Is there some mechanism for that situation?.

An insert is always within a transaction.

If you don't have an explicit BEGIN TRAN ... COMMIT or SET IMPLICIT_TRANSACTIONS ON then the statement runs as a self contained auto commit transaction.

The trigger is always part of the transaction for the action that fires the trigger. If an error occurs in the trigger that causes transaction rollback then the firing action will be rolled back too.

Triggers implicitly have XACT_ABORT on. An error with this setting on will automatically lead to transaction rollback (except for errors raised in the code with the RAISERROR statement).


Cross join flatten JSON - flatten only one the most recent row As reported in this question, i want to flatten a JSON starting from a nested JSON object that contains arrays like this one:
port_name, port_description, port_status_int, port_status, port_speed_int, ports_speed
...
"GigabitEthernet0/0", "", 2, "Not Connect", 1000000000, "1G"
"Bluetooth0/4", "", 2, "Not Connect", 1000000000, "1G"
...

It works, but it flattens all rows that were returned from the query: i need only to flatten the objects that are contained into the first, most recent row (ordered by insert time desc), but when in my clause i'll insert LIMIT 1 it will return only the first flattened json row, why? maybe this is a beginner question, but i'm not able to figure out!
This is the my actual query:
SELECT  mt.measurement_id,
        p.*
from mytable mt
  cross join jsonb_to_recordset(mt.data->'ports') as p(port_name text, port_description text, port_status_int int, port_status text, port_speed_int int, ports_speed text)
where device_id = 20;

Something like this (haven't tried it)
with my_mt as (
  select * from mytable
     where device_id = 20
     order by insert_time desc
     limit 1
) SELECT  my_mt.measurement_id,
          p.*
    from my_mt
    cross join jsonb_to_recordset(my_mt.data->'ports')
                as p(port_name text,
                     port_description text,
                     port_status_int int,
                     port_status text,
                     port_speed_int int,
                     ports_speed text);


Oracle Restore and recover Database on the same host? I have DB1 database on Server1 .
I have taken backup.
After that I have created table TEST.
Inserted some values in the TEST table.
Truncated table.
And want to return my TEST table to its previous state.

For this I took backup(datafiles, archive logs, controlfile, spfile)

I don't want to recover whole database, just this table is needed.
It can be done by restoring and recovering database to another host, export table from the new DB and import to the existing DB(DB1).

Let's assume I have just one server... Can I restore and recover database on the same machine with the new database name?

Waiting your suggestions, I will also try to search something...

N.B. This has been editied since its original posting.

You may want to look at duplicating the database to a new name provided you have all the required backups and archivelogs to restore to the required point in time.

I am assuming that ASM is involved here, if not you might need to look at the duplicate command to see how to rename the files as it's done automatically by ASM.

If your using standard os files rather than ASM then you might need to include this in your duplicate statement once you've connected to the atarget and auxiliary databases (see below);

run
{
  SET NEWNAME FOR DATAFILE 1 TO '/oradata/instance/file1.dbf';
  SET NEWNAME FOR DATAFILE 2 TO '/oradata/instance/file1.dbf';
  <repeat for all the files in your Database>
  DUPLICATE TARGET DATABASE to newdb;
}

Now the theory should be;

Create a new pfile (from the existing database and rename the various references to the database), making sure you change any log destinations and the directories exist

Start the new instance in a no mount state

Then start

rman
Connect target sys@instance
Connect auxiliary /

Once the two connections are up.

The duplicate command to restore to a point in time should be as follows

DUPLICATE TARGET DATABASE TO newdb UNTIL TIME TO_DATE('20/07/2011 19:45:00','DD/MM/YYYY HH24:MI:SS')

-- This should be prior to when you created the Test table so the database will be restored upto that point in time. It theory at least.

another option i do find useful is to resize the logfile and groups

DUPLICATE TARGET DATABASE TO newdb
LOGFILE
      GROUP 1 ('/oradata/instance/redo01a.log',
               '/oradata/instance/redo01b.log') SIZE 200K REUSE,
      GROUP 2 ('/oradata/instance/redo02a.log',
               '/oradata/instance/redo02b.log') SIZE 200K REUSE;

or with ASM something like

DUPLICATE TARGET DATABASE TO newdb
LOGFILE
      GROUP 1 ('+DATA',
               '+FRA') SIZE 200K REUSE,
      GROUP 2 ('+DATA',
               '+FRA') SIZE 200K REUSE;

There are several options you can also specify, I believe one is to an scn, sequence or until a date time. You might need to do a to_date convertion for the last option. putting these together should help you get what you're after.


Hidden operation of rebooting a SQL server database i want to know what operations will be execute in the reboot of SQL server?

The scenario is, the transaction log is almost full, one of my client felt the system is very slow (in fact queries are not executable, and he traced the server CPU & memory usage is very high), thus he choose to reboot the entire server.

Afterwards, he said the operation ran smooth again.

I would like to know, what operations are done in the reboot operation of sql server particular to the transaction log

for example, is a reboot could force the SQL server executing a checkpoint? or even a backup? so that reboot can somehow free the occupied space?

As part of database startup following the reboot, the transaction log for every database is read by SQL Server. Committed transactions are rolled forward, uncommitted transactions are rolled back and the previously active portions of the log would be marked inactive and leave most of the space in the log file free. This process is known as recovery and occurs on database startup, which happens as part of SQL Server startup, AG failover, mirroring failover or after restoring a database from backup.

This may have had an impact on performance, but rebooting the servers does so many things that would affect SQL Server performance. The buffer cache is cleared, as is the plan cache, all open connections are disconnected, any running SQL Agent jobs are cancelled, the list goes on.

The transaction log fullness may have been the problem, or at least part of the problem, but consider a scenario where you have encountered poor plan selection due to parameter sniffing issues. This could easily cause performance issues in SQL Server because the cached plan is not optimal for the number of rows returned by a particular execution of a parameterised query.

This issue has now been masked by a reboot because the plan cache has been cleared. If the first execution of that problem query picked the good plan after the reboot, you may not see this issue for some time until a poor plan is cached for this query again.

Tackling this issue long term (unexpected performance degradation) requires some proactive monitoring:

Gather session and performance statistics using either a paid monitoring tool or free tools like sp_Blitz and sp_whoisactive. Log this information to a table for regular analysis. This also ensures you have information available after your client reboots the server.
Gather your wait stats on a regular basis and log them to a table for analysis.
Ask the client to stop rebooting the server. This wipes many of your valuable statistics from in-memory DMVs and makes identifying and resolving the issue more difficult.
Enable the remote DAC. Next time this occurs, you want resources reserved for an administrator to be able to connect and analyse the server to get to the root cause.
Educate your client - high memory utilisation on a SQL Server is not typically a problem. SQL Server wants memory, it provides best performance when it has access to sufficient memory to store more of the regularly accessed data in cache.
If you have SQL Server 2016 or higher, enable Query Store. This will help you identify unexpected query regressions, such as the one described above. It also helps you implement short-term protections such as forcing known good plans for a query.

Also, take a look at this article which is a great resource for developing a performance troubleshooting methodology in SQL Server, and this article which has some great tips as well.


ORA-01122: database file 1 failed verification check I moved data file and database started fine but I did the following steps:

SQL> shutdown
SQL> startup nomount
SQL> alter system set control_files='/var/oracle/oradata/control.dbf' scope=spfile;
# shut immediate
# chown oracle:dba /var/oracle/oradata/control.dbf
# chmod 755 /var/oracle/oradata/control.dbf
# su – oracle
sqlplus / as sysdba
SQL> startup

Total System Global Area 1068937216 bytes
Fixed Size                  2233344 bytes
Variable Size             813698048 bytes
Database Buffers          247463936 bytes
Redo Buffers                5541888 bytes
Database mounted.

ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/var/oracle/oradata/system.dbf'
ORA-01207: file is more recent than control file - old control file

and now I'm unable to resolve this error.

The SCN in the data file is greater than the SCN in the control file or wrong control file is being used. Please use correct control file.

If the the control fle is correct then do BACKUP CONTROLFILE RECOVERY.

Steps:

SQL>startup mount

SQL> recover database using backup controlfile until cancel;

it will ask you for archive logs, give the path of all archive logs which it needs until it gives you message,

MEDIA RECOVERY COMPLETED.

Open the database.

SQL> alter database open;


passing the parameters of sp_executesql to sp_ineachdb or sp_foreachdb I am often in need to run the sp_executesql in the context of different databases

I would like to add the same parameters as the sp_executesql into the sp_ineachdb

But when I look at this:
-- Syntax for SQL Server, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse

  sp_executesql [ @stmt = ] statement   [      { , [ @params = ]
  N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' }
       { , [ @param1 = ] 'value1' [ ,...n ] }   ]

I can't find out how to define all the parameters to add to the sp_ineachdb.

For example the code below checks for an index inside a database:

    declare @IndexName NVARCHAR(128)='si_xdes_id'
    declare @dbname NVARCHAR(128) = db_name()
    DECLARE @sql NVARCHAR(MAX)
    DECLARE @ParamDefinition NVARCHAR(MAX)
    DECLARE @retval INT

IF @IndexName IS NOT NULL /*validate the index exists in the @dbname database*/
   BEGIN

        SELECT @sql = 'SELECT @retvalOUT = CASE WHEN EXISTS (
                               SELECT * FROM ' + @dbname + '.sys.indexes I WHERE I.Name = @IndexName  )
                               THEN 1 ELSE 0 END '

        SET @ParamDefinition = N'@retvalOUT int OUTPUT,
                                @IndexName SYSNAME';

        EXEC sp_executesql @SQL, @ParamDefinition, @retvalOUT= @retval OUTPUT, @IndexName  = @IndexName;

        IF (@retval = 0)
            BEGIN

               --RAISERROR('The index called %s does not exist on database %s',16,1,@IndexName,@dbname)
               RETURN

            END
   END

for me to use it in conjunction with sp_ineachdb I have to wrap the whole thing into dynamic sql:

    DECLARE @sql NVARCHAR(MAX)=

'   declare @IndexName NVARCHAR(128)=''uc_emailAddress''
    declare @dbname NVARCHAR(128) = db_name()
    DECLARE @sql NVARCHAR(MAX)
    DECLARE @ParamDefinition NVARCHAR(MAX)
    DECLARE @retval INT

IF @IndexName IS NOT NULL /*validate the index exists in the @dbname database*/
   BEGIN

        SELECT @sql = ''SELECT @retvalOUT = CASE WHEN EXISTS (
                               SELECT * FROM '' + @dbname + ''.sys.indexes I WHERE I.Name = @IndexName  )
                               THEN 1 ELSE 0 END ''

        SET @ParamDefinition = N''@retvalOUT int OUTPUT,
                                @IndexName SYSNAME'';

        EXEC sp_executesql @SQL, @ParamDefinition, @retvalOUT= @retval OUTPUT, @IndexName  = @IndexName;

        select @dbname,@retval

        IF (@retval = 0)
            BEGIN

               --RAISERROR(''The index called %s does not exist on database %s'',16,1,@IndexName,@dbname)
               RETURN

            END
   END
'

if object_id('tempdb..#radhe') is not null
   drop table #radhe
create table #radhe(dbname sysname, the_index_exist_here int)
insert into #radhe
EXEC sp_ineachdb @command = @sql, @user_only = 1;

select * from #radhe

which gives me:

question:

is there any easier way to get this done?

I would like to pass to sp_ineachdb  all the parameters that I currently pass to sp_executesql and just gracefully collect the output.

Is that any possible?

Unfortunately, sp_ineachdb was not designed with this specific use case in mind (nested dynamic SQL with parameters), and I don't know if you can do all you want to do with this script without any dynamic SQL at all. I think this approach will be a bit simpler - you'll have to call sp_ineachdb twice, but the dynamic SQL portion is much, much simpler, and you won't have to relay parameters for the procedure to in turn pass along to its own internal sp_executesql call.

DECLARE @indexname nvarchar(128) = N'uc_emailAddress',
        @cmd       nvarchar(max) = N'SELECT db = DB_NAME(),0;';

DROP TABLE IF EXISTS #radhe;

CREATE TABLE #radhe
(
  dbname sysname,
  indexname nvarchar(128),
  the_index_exist_here bit
);

INSERT #radhe(dbname,the_index_exist_here)
EXEC master.dbo.sp_ineachdb @command = @cmd, @user_only = 1;

UPDATE #radhe SET indexname = @indexname;

SET @cmd = N'UPDATE r SET the_index_exist_here = 1
    FROM #radhe AS r
    INNER JOIN sys.indexes AS i
    ON r.dbname = DB_NAME()
    AND i.name = r.indexname;';

EXEC master.dbo.sp_ineachdb @cmd;

SELECT dbname, the_index_exist_here FROM #radhe;


Change data capture - how to know who made the change? Tracking who made the change identified by CDC.

Along the lines of my datetime hack I tried the same approach by adding suser_sname as a new field with default value on the cdc change track table.  But, that seems to return the owner of the cdc process and not the user who initiated the change on the base table.  I also tried original_login but that returns the sql service account login.  Again, likely associated with the cdc process and not the user who initiated the change.

I found a similar question on stack-overflow but with no answer other than tracking changes from the front end or via a trigger, which seems to defeat the purpose of using cdc.  I wouldn't repost but since the original was on stackoverflow I thought I'd give it a try here especially if R2 or 2012 has introduced a better way.

So, in short:  How do I know who made the change in change data capture?

I filed a bug about this, but it was closed as "by design."

http://connect.microsoft.com/SQLServer/feedback/details/283707/cdc-options-to-capture-more-data-username-date-time-etc

Unfortunately you will have to use another technique (e.g. SQL Audit or a trigger) to get this information (and some custom code to try to correlate it to the CDC data as much as you can). I wrote about this deficiency also in my chapter "SQL Server Audit, Change Tracking, and Change Data Capture" in the book SQL Server MVP Deep Dives (volume 1).

I'm sorry that I don't have a better workaround for you but the built-in capabilities of CDC just aren't going to meet your requirements. :-(


Using Same CASE WHEN Conditions For Multiple Query Columns Is there a "better" way to rewrite a SELECT clause where multiple columns use the same CASE WHEN conditions so that the conditions are only checked once?

See the example below.

SELECT
    CASE testStatus
        WHEN 'A' THEN 'Authorized'
        WHEN 'C' THEN 'Completed'
        WHEN 'P' THEN 'In Progress'
        WHEN 'X' THEN 'Cancelled'
    END AS Status,

    CASE testStatus
        WHEN 'A' THEN authTime
        WHEN 'C' THEN cmplTime
        WHEN 'P' THEN strtTime
        WHEN 'X' THEN cancTime
    END AS lastEventTime,

    CASE testStatus
        WHEN 'A' THEN authBy
        WHEN 'C' THEN cmplBy
        WHEN 'P' THEN strtBy
        WHEN 'X' THEN cancBy
    END AS lastEventUser
FROM test

In non-sql psuedo-code, the code might look like:

CASE testStatus
    WHEN 'A'
        StatusCol        = 'Authorized'
        lastEventTimeCol = authTime
        lastEventUserCol = authUser
    WHEN 'C'
        StatusCol        = 'Completed'
        lastEventTimeCol = cmplTime
        lastEventUserCol = cmplUser
    ...
END

Note:

I am aware of the obvious normalization issues implied by the query.  I only wanted to demonstrate the issue.

If all these columns are from the same table, you can use something like this:

    SELECT
        'Authorized' AS StatusCol,
        authTime     AS lastEventTimeCol,
        authUser     AS lastEventUserCol
    FROM test
    WHERE testStatus = 'A'

  UNION ALL

    SELECT
        'Completed',
        cmplTime,
        cmplUser
    FROM test
    WHERE testStatus = 'C'

  UNION ALL

    SELECT
        'In Progress',
        strtTime,
        strtUser
    FROM test
    WHERE testStatus = 'P'

  UNION ALL

    SELECT
        'Cancelled',
        cancTime,
        cancUser
    FROM test
    WHERE testStatus = 'X' ;


Taking hot backup of slave node in postgres (master-slave config with repmgr) I have a postgresql setup with master-slave configuration using repmgr. I'm struggling with taking a consistent snapshot backup (pg_start_backup) of the slave node, this is due the fact that the slave is in recovery, hence read-only mode.

I need both the master and the slave snapshots to speedup the recovery process  (if taken only on one node, in case of recovery the slave needs to be rebuilt from scratch).

Any advice of how to take a consistent snapshot of slave node? (Master is no problem).

Currently these are the approaches I'm checking:

shutting down slave node and take a cold snapshot backup.
pg_start_backup on master and hopefully it will reflect consistent state on slave (with the WAL shipping) - any confirmation on this one?
stop replication, take hot backup of slave using regular methods and then starting replication again - as far as I tested, not possible because as the replication is stopped, each node becomes independent and has its own timeline, thus require the nodes to resync in order to function again in master-slave config.
make a switch over before taking the backup - don't like this option, don't think is a correct approach to make a switch-over each time I want to take a backup.

Anything I missed? Have you implemented it using different methods?

At least on 9.3, you can run pg_basebackup against a hot standby replica, and that's the approach I would recommend. To do this, you must have postgresql.conf set appropriately:

max_wal_senders = 2
hot_standby = on

You didn't specify your version. If you're on a version that can't do a pg_basebackup from a standby you're pretty much out of luck - you need to do a logical dump, dump from the master, or stop the replica for the backup.


How to structure MySQL database tables for users and how to handle adding/removing permissions? I am making a database structure for users, who can become part of a group, and within that group the admin of the group can assign as many permissions to a user as they would like.
This part seems easy. I have 4 tables:
 USERS           GROUPS             PERMISSIONS      PERMISSIONS_GROUPS
-------  ---------------------    --------------    -------------------
| UID |  | GID | UID(creator) |   | PID | NAME |    | UID | GID | PID |

I think this is the best way to handle this. I have a few questions about how to handle permissions for the creator of the group, and how to handle permissions if I were to delete a permission or add a permission.
For instance, when a user makes a group. Do I query all of the permissions and in a while loop insert a PERMISSIONS_GROUPS record for every row in the permissions table? If I add a new permission, do I have to query every UID from the GROUPS table and run a while loop to insert the new permission into PERMISSIONS_GROUPS? I just want to make sure I am not missing something simple here. For structure/scripting I am using MySQL and PHP.

A sketch for an alternative approach would be:
-- supertype for user / group, it is not clear from your post what
-- an appropriate name would be, so I'll just use `x`. each x is of a
-- certain type.

CREATE TABLE x_types
( x_type CHAR(1) NOT NULL PRIMARY KEY
, -- maybe add a description
);

INSERT INTO x_types (x_type) VALUES ('u'), ('g');

CREATE TABLE x
( xid ... NOT NULL PRIMARY KEY
, x_type CHAR(1) NOT NULL
, UNIQUE (x_type, xid)
-- common attributes for user / group
...
);

CREATE TABLE users
( uid ... NOT NULL PRIMARY KEY
, x_type CHAR(1) DEFAULT 'u' NOT NULL
-- user specific attributes
, ...
, CHECK (x_type = 'u') -- does not work in previous version of MySQL
, FOREIGN KEY (x_type, uid) REFERENCES x (x_type, xid)
);

CREATE TABLE groups
( gid ... NOT NULL PRIMARY KEY
, x_type CHAR(1) DEFAULT 'u' NOT NULL
-- group specific attributes
, group_creator ... NOT NULL
, ...
, CHECK (x_type = 'u') -- does not work in previous version of MySQL
, FOREIGN KEY (x_type, gid) REFERENCES x (x_type, xid)
, FOREIGN KEY (group_creator) REFERENCES users (uid)
);

-- a relationship between supertype and permission
CREATE TABLE x_permissions
( xid ... NOT NULL
, pid ... NOT NULL
, PRIMARY KEY (xid, pid)
, FOREIGN KEY (xid) REFERENCES x (xid)
, FOREIGN KEY (pid) REFERENCES permissions (pid)
);

-- group membership for users
CREATE TABLE group_users
( gid ... NOT NULL REFERENCES groups (gid)
, uid ... NOT NULL REFERENCES users (uid)
, PRIMARY KEY (gid, uid)
);

This may suit your needs, but it is hard to tell without knowing the exact business rules. Identifiers are of course just bogus and need to be adjusted.


Import an xlsx file to Oracle SQL Developer and interpret it as a table I have an xlsx file with content in the form of a database table - first row is column names, and columns contain varchars2, integers etc.

Since VBA and Excel worksheet functions are too slow for it, I want to view and manipulate it in Oracle SQL Developer, Version 19.2.0.206 developer. Everything occurs locally, there is no existing database connection, and none available.

I attempt to do this by clicking "new connection" (below screenshot; in German), but it prompts me to enter information for a database connection that I cannot have, because there is none yet.

When I try to make up a username, password, and use the below seen default hostname, port etc., and click on test, it shows: I/O error: The network adapter could not establish the connection.

Is it possible, and how do I achieve, that there is a "dummy" "connection" that is not actually connected to anything existing, but allows me to turn a suitable xlsx file into a table for data manipulation?

I have access to MS Access, but I hate the interface and have more experience with Oracle SQL, so I would appreciate a solution with Oracle SQL

No.  SQL Developer is just a client that interacts with an Oracle database.  It is not a database in and of itself so it has no way to store and manipulate data other than via an Oracle database.

You could download and install an Oracle database on your machine, connect SQL Developer to that, and use the SQL Developer wizard to load the Excel file into a table you create in this new Oracle database. But that is a decent amount of effort to go through just to deal with a single spreadsheet.


What is a practical use of FOR STATEMENT triggers? I understand very well the idea behind triggers, how and when to use them.

I also see the practical use of a FOR EACH ROW trigger.

But I lack to find a concrete example of when I would use a FOR STATEMENT trigger. What could I do with it?

Can anyone give me an example of a real life use of that?

I cannot speak to all database platforms that support statement level triggers, but DB2 LUW also supports both row level triggers AND statement level triggers.

Referencing the documentation for DB2 LUW (highlighting mine) - Specifying what makes a trigger fire (triggering statement or event)

  When a trigger is activated, it runs according to its level of
  granularity as follows:

  FOR EACH ROW

  It runs as many times as the number of rows in the set of affected rows.

  If you need to refer to the specific rows affected by the
  triggered action, use FOR EACH ROW granularity. An example of this is
  the comparison of the new and old values of an updated row in an AFTER
  UPDATE trigger.

  FOR EACH STATEMENT

  It runs once for the entire trigger event.

  If the set of affected rows is empty (that is, in the case of a
  searched UPDATE or DELETE in which the WHERE clause did not qualify
  any rows), a FOR EACH ROW trigger does not run. But a FOR EACH
  STATEMENT trigger still runs once.

For example, keeping a count of number of employees can be done using FOR EACH ROW.

CREATE TRIGGER NEW_HIRED
  AFTER INSERT ON EMPLOYEE
  FOR EACH ROW
  UPDATE COMPANY_STATS SET NBEMP = NBEMP + 1

You can achieve the same affect with one update by using a granularity of FOR EACH STATEMENT.

CREATE TRIGGER NEW_HIRED
  AFTER INSERT ON EMPLOYEE
  REFERENCING NEW_TABLE AS NEWEMPS
  FOR EACH STATEMENT
  UPDATE COMPANY_STATS
  SET NBEMP = NBEMP + (SELECT COUNT(*) FROM NEWEMPS)

Some examples of why you might want to use a statement level trigger over a row level trigger:

You want to record how many rows were updated, but don't necessarily
care about which rows were updated
You want to execute an action when a statement is made against a
table, even if no rows are updated
Your table has 40 Billion rows and sometime updates are made against
10% of the table. Recording an action for 4 Billion rows, one at a
time, is not feasible due to resource constraints
You want to record any time any person makes an update to a table,
but you don't care what the update was, and you don't care how many
rows were affected.


Duplicate records referenced across different databases I have a table holding 'People' data (about 70K records) that coexists with an 'Address' table so there's one AddressID per every person in the 'People' table.

The idea was to centralize the data in this single 'Population' database, problem started once duplicated records started to appear due to a poorly executed Import process (Data coming from different sources), this left the table with as much as 7 records for a single Person.

Additionally the Address table started collecting 'all sorts', so let say I live in "24 Wickam Heights" you can found this address in the following ways:

24 Wickam
24 Wickam St
24 Wickam Street
24 Whikam H.
24 Wikam Str.

In some cases as bad as over 20 different versions of the same street...

The most beautiful part is that the data from this database is being referenced from at least 5 other databases in the same server, making every change a very risky process.

So I'm thinking, what steps can be taken to get rid of the duplicates?, What alternatives there is for avoiding the address table accumulating such an amount of data inconsistency?.

Maybe even to ask, is there any salvation for such a big mess? It really is a nightmare.

There are some great data cleansing products out there, one in particular that is actually top notch and affordable. I've come across DataMatch by Data Ladder, which is an excellent fuzzy matching and address standardization/address parsing tool used across business and would work really well for this situation. They offer a complimentary trial for new users.

In fact, an independent verified evaluation was done of the software comparing it to major software tools by IBM and SAS. There was a study done at Curtin University Centre for Data Linkage in Australia that simulated the matching of 4.4 Million records. It identified what providers had in terms of accuracy (Number of matches found vs available. Number of false matches)

1.  DataMatch Enterprise, Highest Accuracy (>95%), Very Fast, Low Cost
2.  IBM Quality Stage , high accuracy (>90%), Very Fast, High Cost (>$100K)
3.  SAS Data Flux, Medium Accuracy (>85%), Fast, High Cost (>100K)


How can a key lookup with zero executions and zero actual rows take time? I have a query that is taking about 10 seconds to run and I am trying to optimize it.

All the time is spent in the key lookup which is fine however in this case if I mouse over the key lookup in the actual execution plan it says

Expected Rows : 1
Estimated Executions : 3341
Actual Rows : 0
Executions : 0

I understand in this case the statistics are expecting the index seek to find rows which is fine but as no rows were returned in the Index Seek why would the key lookup take any time? or even be shown in the query plan at all?

The percentages on each operator that are shown in an "actual" plan are still estimates. The additions to an "actual" plan over "estimated" are the actual row counts and operator executes.

In the example you've given the key lookup was estimated to be executed 3341 times, which is why it has a high estimated percentage cost.

One of the other operators is really consuming the time. Get a (free) copy of Plan Explorer from Sentry One, it'll make it easier to identify where.

If nothing stands out, start a new question and post the execution plan xml.


max_connections error during restoring PostgreSQL to Local Database I am facing max_connections error during database restoration on ApsaraDB for PostgreSQL in a local database.

I am using below mentioned command for database restoration

\i c:/mydb1.sql

This error is caused by the large number of connections. To solve this problem, we recommend that you set the max_connections values of both the master and slave instances to 800 or less.

The max_connections values for the master and slave instances are required to be the same. For details, see Server Configuration and Server Setup and Operation on the official website of PostgreSQL.

Please Click here for detailed information.


Generate rows based on value in 2 columns. Bug? I have the following table in Oracle:

  ID     START  END
_ID01   AB050   AB100
_ID01   AB101   AB150
_ID02   AB0251  AB0300
_ID01   AB00301 AB00302

I want to generate as many rows and every records defines. So the first need 50 more with a new col start with AB50 up to AB100. Everything works fine, but if I don't add that junk row, the sql developer can't solve the problem, just loading.

I tried with some random SQL hint but none of them worked. Need some advice or hint what's "wrong" with my working query, because I don't think that's normal behavior.

And my SQL query is the following.

     with m as (
      select  to_number(substr(t.START,3)) as num
      from FCS_FP_TOMB t
      where t.START ='AB0251' --<--- THIS ROW IS A JUNK BUT DOESNT WORK WITHOUT IT
     ),
      n as (
      select level - 1 as n
      from m
      connect by level <= m.num + 1
     )
select  ft0.*, regexp_substr(ft0.START,'([A-Z])+')||to_char(to_number(substr(ft0.START,3)) + n.n) as "bizonylat"
from FCS_FP_TOMB ft0 join
     n
     on to_number(substr(ft0.START,3)) + n.n <= to_number(substr(ft0.END,3))

 order by 2,3,7
     ;

The out put:

...
_ID01   AB050   AB100   AB098
_ID01   AB050   AB100   AB099
_ID01   AB050   AB100   AB100
_ID01   AB101   AB150   AB101
_ID01   AB101   AB150   AB102
...

EDIT: I tried MATERIALIZE and INLINE hints, and my Oracle server is 10g.

You have an error in your query. The CTE m will return not a single row, but as many rows as in FCS_FP_TOMB. Then you are putting all of those rows into the row generator CTE n, whereas a row generator construct like you're using should get a single row.

What I would do is to define m to return a single row with the minimum and maximum values, and then subtract these in n to get the number of rows you need to generate.

Something like (untested):

with m as (
  select max(to_number(substr(t.END,3))) - min(to_number(substr(t.START,3))) as num
  from FCS_FP_TOMB t
), n as (
  select level - 1 as n
  from m
  connect by level <= m.num + 1
)
select ft0.*, regexp_substr(ft0.START,'([A-])+')||to_char(to_number(substr(ft0.START,3)) + n.n) as "bizonylat"
from FCS_FP_TOMB ft0
join n on to_number(substr(ft0.START,3)) + n.n <= to_number(substr(ft0.END,3))
order by 2,3,7;


Maintaining SQL Server 2008 R2 on Windows 10 I have to run SQL Server 2008 R2 on Windows 10 but it is not supported and so I am not getting any updates. Installing a later version of SQL Server is not an option. I can find all the service packs and cumulative updates online.

Questions:

Is there a way to get updates automatically?
If not, do I have to install all the service packs in sequence, or can I install only the most recent.

Is there a way to get updates automatically?

SQL Server 2008 R2 is not supported on Windows 10. This means the SQL Server team will not be releasing any security updates or patches if they find any specific issues related to SQL Server 2008 R2 code. There is no way to get updates automatically as none would be released related to Windows 10.

Having said that, SQL Server 2008 R2 installation is not blocked when you try to install it on Windows 10. Of course, it is always better and advisable to install the latest Service Pack (Service Pack 3 is the final SP for 2008 R2).

If you are trying to install SQL Server 2008 R2 for testing purpose on Windows 10, you can go ahead; but since it's not supported it would be of no use to carry forward this idea.

  If not, do I have to install all the service packs in sequence, or can I install only the most recent?

Service Packs are cumulative, so you can directly install SQL Server 2008 R2 SP3 even on RTM bits. There are cumulative updates (CU) released after 2008 R2 SP3 as well, but my advice is don't install CU unless you face specific issues fixed in the CU.


PostgreSQL Foreign Key Very basic questions.
I have 3 tables, very obvious whats the use of them:

--------
CONTACT
--------
ID (SERIAL) - PK
NAME (VARCHAR)

---------------
CONTACT_DETAILS
----------------
ID (SERIAL) - PK
ID_CONTACT (INT)
ADDRESS (VARCHAR)
BIRTH (DATE)

--------
SALES
--------
ID (SERIAL) - PK
ID_CONTACT) (INT)
AMOUNT (NUMERIC)
DATE_PAID (DATE)

1) Should I create a Foreign Key between CONTACT and CONTACT_DETAILS?
If yes, should be CONTACT_DETAILS (ID_CONTACT) ---> CONTACT (ID) or inverse? And you recommend that its cascade?

2) Same question, should I creat a Foreign Key between SALES table and CONTACT?

3) It is a good pract every table have an "ID" serial column, so its easy to identify or there's no need?

Foreign keys

Foreign keys are good to keep the database consistent. The common practice is to name the foreign key starting with the name that it refers to then the name of the column: instead of id_contact use contact_id.

ID column

There are a couple of benefits of serial primary key.

Faster insert speed
Easier to refer to the row if necessary
Some frameworks requires single column PK (for example Django)

Also it's possible to use natural primary key like email address or personal identification number or composite keys. The benefits are:

Lookup on these are faster

Usually insert speed are lower because insert happens out of order and results in more fragmented table.

This really depends on many things and without context it's hard to give recommendation. You need to take into consideration the application using this database, the relationships, insert/update/select rate, etc. Try and see what works best for your case.

Schema

I would start with something like this:

Since it's unlikely that a contact will have more than one birthday splitting it is unnecessary. You can though have a different address table if it's possible to have two or more address for a single contact. The simplest solution would be:

create table contact (
    id serial NOT NULL,
    name text not null,
    birth date,
    address text,
    PRIMARY KEY (id)
);

create table sales (
    id serial NOT NULL,
    contact_id integer NOT NULL REFERENCES "contact" ("id") DEFERRABLE INITIALLY DEFERRED,
    amount numeric NOT NULL,
    date_paid date NOT NULL,
    PRIMARY KEY (id)
);

Bare in mind that postgresql doesn't create an index automatically for foreign keys (more info: https://stackoverflow.com/questions/970562/postgres-and-indexes-on-foreign-keys-and-primary-keys). If you need it you have to create it explicitly:

CREATE INDEX sales_contact_fk_idx ON sales (contact_id);


Using JSONB Column or Another Table to Save Relationsips I tried to search here thoroughly but did not find any answers.

I have a PostgreSQL database, which has two main tables:

documents
users

These two tables have different relations. A user can:

like
bookmark to read later
save

... a document.

The question is how should I save these relations?

In my experience with MySQL, the obvious way was to create tables for these many to many relations, containing user_id and document_id.

But as we are using PostgreSQL and it has the amazing JSON support, we are thinking maybe the better approach is to have a user_document table, which contains user_id, document_id and a JSON column containing all the relations.

The JSON would be something like this:

{
   'follow' : {'date' : 1523517140, 'doesFollow' : 't'},
   'bookmark' : {'date' : null, 'doesBookmark' : 'f'},
   ....
}

I have little to zero experience with PostgreSQL and I don't know about the performance of querying on JSONB columns. And I don't know if this approach makes sense at all in PostgreSQL. But it seems OK and if there is nothing wrong with it, maybe it is preferable to the first, normal, approach.

Document types like json or jsonb (or xml or hstore) are convenient to store, well, documents. Ideal for data with varying keys and rarely updated and not-too-complex filter criteria in queries. Manipulating big documents a lot inside the database would be an anti-pattern.

Structured data that is often written in small increments, and probably searched a lot (like in your case most likely) are much more efficient with a normalized design - regarding storage, performance, concurrent write access and data integrity. Hence the advice in the manual:

  JSON data is subject to the same concurrency-control considerations as
  any other data type when stored in a table. Although storing large
  documents is practicable, keep in mind that any update acquires a
  row-level lock on the whole row. Consider limiting JSON documents to a
  manageable size in order to decrease lock contention among updating
  transactions. Ideally, JSON documents should each represent an atomic
  datum that business rules dictate cannot reasonably be further
  subdivided into smaller datums that could be modified independently.

Implement your n:m relationship with one ore more junction tables. You can enforce data integrity with constraints (PK, FK, UNIQUE, CHECK, ..), none of which is easily possible with document types. I would lean towards a single table unless you have substantially differing requirements for "like", "bookmark" etc.

Example:

If you are unsure about the typical layout, here are the basics:

How to implement a many-to-many relationship in PostgreSQL?

Assuming that each of your relation types can only be used once per user/doc combination. For just a hand full of values (3 in your case) I would use a 1-byte "char" column as PK of a lookup table to optimize storage and performance in tables and indexes.

CREATE TABLE reltype (
   reltype "char" PRIMARY KEY
 , relation_type text UNIQUE NOT NULL
);

INSERT INTO reltype(reltype, relation_type) VALUES
   ('l', 'like')
 , ('b', 'bookmark')
 , ('s', 'save');

CREATE TABLE user_doc (
   user_doc_id int PRIMARY KEY GENERATED ALWAYS AS IDENTITY
 , user_id     int REFERENCES users     ON UPDATE CASCADE ON DELETE CASCADE
 , document_id int REFERENCES documents ON UPDATE CASCADE ON DELETE CASCADE
 , reltype     "char" REFERENCES reltype NOT NULL DEFAULT 'l'
 , CONSTRAINT user_document_pkey UNIQUE(user_id, document_id, reltype)
);

You can always export aggregated data as JSON documents. Even have a VIEW or MATERIALIZED VIEW to read like a table. But don't manage relationships inside a single big JSON document.


How can I Insert when not in table and when max timestamp T-SQL I am trying to insert rows from one table to another that are not in the one I am moving them to. I also want to only move the ones that have the highest datestamp. (I want to only insert rows that are not in tb1 and have the max timestamp)

This is what I have so far:

INSERT INTO [db].[dbo].[tb1]
SELECT *
FROM tb2 WHERE(dbo.tb2.STime = (SELECT MAX(STime) FROM dbo.tb2))
  AND (EMPNO NOT IN (SELECT EMPNO FROM [db].[dbo].[tb1]));

I get this error when I execute:

  Msg 147, Level 15, State 1
  An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

I used a CTE with just one column that is the maxtime from tb2. Then join on the CTE and reference it when checking for if its the max or not.

WITH aggregateTime (maxTime) AS (
SELECT MAX(STime) AS maxTime
FROM tb2
)
INSERT INTO db.dbo.tb1
SELECT *
FROM tb2
INNER JOIN aggregateTime ON 1=1
WHERE tb2.STime = aggregateTime.maxTime AND EMPNO NOT IN (SELECT EMPNO FROM tb1);


How can I speed up this simple query I am trying to speed a query that grabs data to be used to display a graph for a research project. It's currently VERY slow. It has 18295603 items only and takes about 20s per query. Here is an example query:

SELECT `data_dylos`.`id`, `data_dylos`.`node_id`, `data_dylos`.`dylos_bin_1`,
`data_dylos`.`dylos_bin_2`, `data_dylos`.`dylos_bin_3`,
`data_dylos`.`dylos_bin_4`, `data_dylos`.`big_particles`,
`data_dylos`.`small_particles`, `data_dylos`.`added_on`,
`data_dylos`.`last_modified`, `data_dylos`.`reading_time` FROM `data_dylos`
 WHERE (`data_dylos`.`added_on` > '2014-08-06 23:00:24'  AND
`data_dylos`.`node_id` = 3 ) LIMIT 21

The added_on value is calculated before the call is made.

Here is my show create table data_dylos:

CREATE TABLE `data_dylos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `node_id` int(11) NOT NULL,
  `dylos_bin_1` int(11) DEFAULT NULL,
  `dylos_bin_2` int(11) DEFAULT NULL,
  `dylos_bin_3` int(11) DEFAULT NULL,
  `dylos_bin_4` int(11) DEFAULT NULL,
  `reading_time` datetime DEFAULT NULL,
  `added_on` datetime NOT NULL,
  `last_modified` datetime NOT NULL,
  `big_particles` int(11) DEFAULT NULL,
  `small_particles` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `data_dylos_1eac40ef` (`node_id`),
  KEY `data_dylos_aa73cdcc` (`added_on`)
) ENGINE=InnoDB AUTO_INCREMENT=20029122 DEFAULT CHARSET=latin1

The fields of interest ARE already indexed but it is still very slow.

Any help will be greatly appreciated.

Thanks!

This is as you say a very simple query, so the optimal index here is very clear. Thanks for showing the whole table structure, indexes and full query, not all people do that.

You indexes are in the good direction, but you must know that (in general, there are other query plans like index_merge) a table scan can only use one index. In this case, as you are filtering by two columns, you have to add the multi-column index:

ALTER TABLE data_dylos ADD INDEX (node_id, added_on);

You have to create it on that order (node_id, added_on) and not in reverse (added_on, node_id), as that would be a different index. The difference is that in a BTREE index, after a range join type is used, the rest of the index is useless.

That is the optimal index query-wise. If you continue to have performance problems you may want to review your configuration (specially innodb_buffer_pool_size).

There are additional techniques that may help you, like caching or partitioning, but those are more advanced, or may require more structure changes, so try this first.


How to merge two databases, both with primary keys I have two databases, both with the same tables, but the data is out of sync.

I need to add data from database A to database B, but not overwrite any data in B that exists in A, instead add it with a new ID.

E.G

Table users in B has a row with ID (auto increment, primary) 5, for a guy called Bob. Table users in A has a record for a guy called Dave in row ID 5.

I'd like Dave to be added to B.users but with a new ID.

I have looked at using Navicats Data Sync, but there are no advanced options to allow this, only to stop it overwriting (updating) or removing the record.

What are my options here? Is it a manual job?

I assume there is a UNIQUE index on the column with "Dave" and "Bob"?  Without that, how do you know what is "new"?

Let's call the column name (distinct from id).

Does some other table depend on Dave=5?  If so, the following will not suffice, and the solution is much messier.

Are there changes to the columns?  Or is it only "new" rows to be copied from A to B?

INSERT INTO B.user
    SELECT ...  -- excluding `id`
        FROM A.user
        LEFT JOIN B.user  USING(email)
        WHERE B.id IS NULL;     -- Not currently in B

To handle changes, look at INSERT ... ON DUPLICATE KEY UPDATE, again with a JOIN, but this time IS NOT NULL.


Can't uninstall SQL Server 2008 R2 SP3 I have encountered the error on SQL Server 2008 R2 where Report Builder 3 fails to load with error

  Microsoft.ReportingServices.ComponentLibrary.Controls.dll, has a different computed hash than specified in manifest.

As described in this SQL Release Services blog post, this problem should be fixed by uninstalling SP3, uninstalling SP2, then re-installing SP3.

However I can't seem to uninstall SP3. When I navigate to Programs and Features -> View Installed Updates, click on SQL Server SP3, and choose Uninstall, I get a wizard window that looks like this:

There is no way to proceed. How do I move forward with this issue so that my users can get back to using Report Builder?

I know this is an older post, but to save anyone else a headache...

You will only be able to remove SP3, if you have already removed all other patches that have been applied since the SP3 install, you can do this via Windows Programs and features -> View Installed Updates, and checking all patch versions greater than the one for SP3.


How Do I Query this Data in Access Without Creating a Circular Reference I have two tables called Events and EventSeasons. Events define the main type of events that there can be, and EventSeasons stores the number of times that the events have occurred yearly. I'm trying to query them so that I can figure out which ones are ongoing (between a range of dates), but my problem is that I also need to retroactively mark past seasons of the same event type as active. The next diagram shows what the output should be:You can see in EventSeason that IsActive column for item 2003 is true. Therefore in Events the Subactive column for the item 1001 (Summer) is True. Finally back in EventSeasons the Retroactive column for all 1001 items is set to true.

I have actually achieved this in Excel, but now that I'm trying to do it in access I feel like I'm going to create a circular reference. What I did in excel was to assign true to IsActive in EventSeasons if today's date is between range, then I assign true to Subactive in Events if there are instances of the current EventID marked as active in EventSeason. I then finally assign RetroActive to true if Subactive is true Or IsActive is true.

It would probably be easiest to do what you are trying to do by using views, instead of trying to add calculated columns to the table.

EventTheme_IsActive

SELECT ThemeID,
       EventID,
       StartDate,
       FinishDate,
       IIf(Date()>=StartDate And Date()<=FinishDate,True,False) AS IsActive
FROM EventTheme;

Events_Subactive

SELECT e.EventID, e.Name, et.Subactive
FROM Events AS e
LEFT JOIN (
    SELECT EventID, IIF(COUNT(*) > 0, True, False) AS Subactive
    FROM EventTheme_IsActive
    WHERE IsActive = TRUE
    GROUP BY EventID)  AS et ON e.EventID = et.EventID;

Events_RetroActive

SELECT et.ThemeID, et.EventID, et.StartDate, et.FinishDate, et.IsActive, a.RetroActive
FROM EventTheme_IsActive AS et
LEFT JOIN (
    SELECT EventID, IIF(COUNT(*) > 0, TRUE, FALSE) AS RetroActive
    FROM EventTheme_IsActive
    WHERE IsActive = TRUE
   GROUP BY EventID)  AS a ON et.EventID = a.EventID;

These views may not be updateable but if you just need read access then this should suit your needs.


Exporting table from MySQL 5.7 to MySQL 5.6 (Error) While running the following queries on MySQL 5.6.39,

DROP TABLE IF EXISTS `sessions`;

CREATE TABLE `sessions` (
  `id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_id` int(10) UNSIGNED DEFAULT NULL,
  `ip_address` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_agent` text COLLATE utf8mb4_unicode_ci,
  `payload` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `last_activity` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `sessions`
  ADD UNIQUE KEY `sessions_id_unique` (`id`);

I get the following error :

However, if I remove the part that says COLLATE=utf8mb4_unicode_ci, everything works fine. Why is this happening ?

The above query is an export from phpMyAdmin (MySQL version 5.7.22)

It seems that the id column is varchar(255) so from your config file enable the innodb_large_prefix on this instance of MySQL to resolve the problem but per the below, this explains why this is occurring from my interpretation.

You can run show variables and see if the innodb_large_prefix if ON or OFF.

Maximums and Minimums

  By default, the index key prefix length limit is 767 bytes. See
  Section 13.1.13, “CREATE INDEX Syntax”. For example, you might hit
  this limit with a column prefix index of more than 255 characters on a
  TEXT or VARCHAR column, assuming a utf8mb3 character set and the
  maximum of 3 bytes for each character. When the innodb_large_prefix
  configuration option is enabled, the index key prefix length limit is
  raised to 3072 bytes for InnoDB tables that use DYNAMIC or COMPRESSED
  row format.

  Attempting to use an index key prefix length that exceeds the limit
  returns an error. To avoid such errors in replication configurations,
  avoid enabling innodb_large_prefix on the master if it cannot also be
  enabled on slaves.

  The limits that apply to index key prefixes also apply to full-column
  index keys.


Why sys.columns.is_nullable can be null? I'm writing a query that will be mapped to a C# class, and when getting the type of the query's columns, it results that some columns in sys.columns can be NULL

is_nullable
is_replicated
is_merge_published
is_dts_replicated

And in the same sys.columns there are others that are NOT NULL

is_rowguidcol
is_identity
is_computed

NOTE: Those columns are bit

I think they should always be NOT NULL, because SQL Server must know the columns' properties.

So, why some/those columns in sys.columns can be NULL and/or in which cases they will be?

You are right that it is not possible for the outcome to ever be NULL. From quick investigation, take a look at the definition of sys.columns. If I run:

SELECT OBJECT_DEFINITION(OBJECT_ID(N'sys.columns'));

I see this:

...
sysconv(bit, 1 - (c.status & 1)) AS is_nullable,        -- CPM_NOTNULL
...
FROM sys.syscolpars c
...

If I change to a DAC connection, I can look at the columns here:

SELECT name, is_nullable
  FROM sys.all_columns
  WHERE [object_id] = OBJECT_ID(N'sys.syscolpars')
  AND name = N'status';

Results:

name    is_nullable
------  -----------
status  0

So the source is definitely not nullable, right? Yet if you do the same for sys.columns, columns involving expressions around c.status are marked as is_nullable = 1. That's more a reflection of the expressions / data types involved than the actual possibility of a NULL value.

I initially thought maybe SYSCONV() did something differently from CONVERT() (we can't use the former to check, but I thought maybe it might internally work more like TRY_CONVERT()). This is not the case, in fact this is quite easy to reproduce even without any internals knowledge:

CREATE TABLE dbo.foo(bit_column bit NOT NULL);
GO

CREATE VIEW dbo.bar
AS                                                      -- nullable?
  SELECT bit_col1 = CONVERT(bit, 1 - (bit_column & 1)), -- YES
         bit_col2 = bit_column & 1, -- no convert       -- NO
         bit_col3 = 1 - bit_column & 1                  -- YES
  FROM dbo.foo;
GO

SELECT name, is_nullable
  FROM sys.dm_exec_describe_first_result_set
       (N'SELECT * FROM dbo.bar', NULL, 1);
GO

DROP TABLE dbo.foo;
DROP VIEW dbo.bar;

Results:

name      is_nullable
--------  -----------
bit_col1  1
bit_col2  0
bit_col3  1

All that said, I don't know what advice to give you. You could conditionally hard-code that this specific column is not nullable in spite of what the metadata says, or you could play it safe and use what the metadata tells you (which will future-proof you in the event the underlying definitions change in future versions).


Index of which columns are NOT empty I am trying to enhance the search performance of a table based on whether certain columns contain values.

I have the following columns :

id |
link |
text |
date |
lat |
long |
type

If a record has a value in lat & long it can be shown on a map. It’s considered a “dot”

If a record has a value in date & lat & long it is considered an “event”

If a record has a value in type and text it is considered “news”

With over 1 million records in the table, what is the best way to index so I can quickly extract all “dots” or all “events” or all “news”?

To reiterate, I don’t care to index the actual values of these columns, only if there is ANY value in that column.

I tried creating an index of multiple columns but it used the values of the columns, and so many often unique values resulted in slow performance.

I considered using a “hash” based on which columns were not null, but I couldn’t extract those 3 variations beaded on the hash result.

Any advice appreciated.

Use binary (or some integer as binary) or SET generated encoding column. For example:

CREATE TABLE tablename
( id SERIAL PRIMARY KEY,
  linkfield  VARCHAR(255),
  textfield TEXT,
  datefield DATE,
  lat DECIMAL(10,8),
  long DECIMAL(10,8),
  type ENUM('value1', 'value2', ... , 'valueN'),
  record_type_mask TINYINT UNSIGNED
                   AS ( (linkfield  IS NOT NULL) *  1 +
                        (textfield  IS NOT NULL) *  2 +
                        (datefield  IS NOT NULL) *  4 +
                        (lat + long IS NOT NULL) *  8 +
                        (type       IS NOT NULL) * 16
                      )
);

then use either constant values.

For example, if you want to select

  If a record has a value in date & lat & long it is considered an “event”

you will use

WHERE record_type_mask = 12

because the value 4 marks present lat and long, and the value 8 marks datefield.

If you need records which have datefield set you may use masking:

WHERE record_type_mask & 4
-- or
WHERE record_type_mask IN (4,5,6,7,12,13,14,15,20,21,22,23,28,29,30,31)


How to filter splitting a column value? I´m a newbie in the SQL world and I code with just the simple insert, select, delete and update.

So... I have a table with a column named "market" that has a list of cryptocoins... like this...

market
-------
EOS/ETH
EOS/BTC
XMR/BTC
LTC/BTC
DASH/ETH
...

I need to select * from this table where:

the string before the '/' is equal to each other but after the '/' goes ETH and BTC.
There is a column named exchange that I want to filter too. In this example would be all this coins where exchange = 'bittrex'

In this example the result would be:

EOS/ETH
EOS/BTC

Can anyone help?

You can use string functions like INSTR(), LEFT(), RIGHT(), LENGTH() for this purpose.

create table market(id int, col varchar(100), exchange varchar(100));

insert into market values
(1, 'EOS/ETH',  'bittrex'),
(2, 'EOS/BTC',  'other'),
(3, 'EOS/BTC',  'bittrex'),
(4, 'XMR/BTC',  'other'),
(5, 'LTC/BTC',  'other'),
(6, 'DASH/ETH', 'other');

select *
from   market t1
where  right(t1.col, length(t1.col) - instr(t1.col, '/')) IN ('ETH','BTC')
and    exchange = 'bittrex'
and    exists (select 1
               from market t2
               where left(t2.col, instr(t2.col, '/')) = left(t1.col, instr(t1.col, '/'))
               and right(t2.col, length(t2.col) - instr(t2.col, '/')) IN ('ETH','BTC')
               and   exchange = 'bittrex'
               group by left(t2.col, instr(t2.col, '/'))
               having count(*) > 1)

id | col     | exchange
-: | :------ | :-------
 1 | EOS/ETH | bittrex
 3 | EOS/BTC | bittrex

dbfiddle here
As Rick James has pointed out on his comments you can use SUBSTRING_INDEX() and get the same result.

select *
from   market t1
where  substring_index(t1.col, '/', -1) IN ('ETH','BTC')
and    exchange = 'bittrex'
and    exists (select   1
               from     market t2
               where    substring_index(t2.col, '/', 1) = substring_index(t1.col, '/', 1)
               and      substring_index(t1.col, '/', -1) IN ('ETH','BTC')
               and      exchange = 'bittrex'
               group by substring_index(t1.col, '/', -1)
               having   count(*) > 1)

id | col     | exchange
-: | :------ | :-------
 1 | EOS/ETH | bittrex
 3 | EOS/BTC | bittrex

dbfiddle here


How to insert nested JSON arrays into a table? I'm trying to insert a JSON array that came from another JSON array (array of arrays) into a table using json_populate_recordset(), but I'm receiving the following error:

SQL Error [22023]: ERROR: argument of json_populate_recordset must be an array of objects

Here's an example of what I'm trying to do:

DO $$
DECLARE
    j json;
BEGIN
    j := '[[{"a":1,"b":1.23},{"a":2,"b":2.34}],
           [{"a":2,"b":1.23},{"a":3,"b":2.34}],
           [{"a":3,"b":1.23},{"a":4,"b":2.34}]]'::json;

    CREATE TEMPORARY TABLE testj (j json);

    INSERT INTO testj
    SELECT * FROM json_populate_recordset(null::testj, j);

    DROP TABLE testj;
END
$$;

The final table should be something like this:

                 j                  |
------------------------------------|
[{"a":1,"b":1.23},{"a":2,"b":2.34}] |
[{"a":1,"b":1.23},{"a":2,"b":2.34}] |
[{"a":1,"b":1.23},{"a":2,"b":2.34}] |

I'm using a PostgreSQL 9.5 database. What am I doing wrong?

Use the function json_array_elements() instead, available in Postgres 9.5.
Quoting the manual, it ...

  Expands a JSON array to a set of JSON values.

And values can in turn be JSON arrays (or any other JSON values).

INSERT INTO testj (j)
SELECT * FROM json_array_elements(j);


How does to_tsquery and to_tsvector decide which characters to omit? I was quite surprised to find that TO_TSVECTOR('english', '平仮名ひらがな'); results in a vector of '平仮名ひらがな':1. For a system that omitted all non-alphanumeric characters and emojis I threw at it, I expected it to also omit all foreign characters; i.e. the desired behavior is to only index alphanumeric characters.

How does it decide which characters to omit (is it a whitelist/blacklist, a range of character codes, etc)? I don't really mind it automatically converting accented characters to their normal alphanumeric counterpart, but I would like to efficiently omit all characters that don't have an alphanumeric representation.

edit - based from jjane's answer, this is the final solution I'm going with:

CREATE TEXT SEARCH CONFIGURATION basic (parser = pg_catalog.default);
ALTER TEXT SEARCH CONFIGURATION basic ADD MAPPING FOR asciiword, asciihword, hword_asciipart WITH english_stem;
--optionally ALTER TEXT SEARCH CONFIGURATION basic ADD MAPPING FOR email, host WITH english_stem;

They are alphanumeric in some alphabet, just not the one you want to use.

You can alter your text search configuration to drop the types of tokens you do not want.  You should probably copy and alter, not alter in place.

CREATE TEXT SEARCH CONFIGURATION ascii_only ( COPY = pg_catalog.english );
alter text search configuration ascii_only drop mapping for word;
select * from to_tsvector('ascii_only', '平仮名ひらがな dogs');

Yields:

"'dog':1"

This is just a minimal example, you will probably need to drop the mappings for other token types as well, chosen from this list.  You might not be able to get exactly what you want, for example there is no asciinumword, so there is no way keep 'dog1' but reject '平仮名ひらがな1'.  So in that case you would have to implement your own non-default parser, or use functions (hopefully making them inefficient is optional--we might be able to help with that).

You can use ts_debug to see what is going on.  A word that is entirely ASCII letters [a-zA-Z] gets tokenized to 'asciiword', and not tokenized to 'word'.  So a better name for 'word' might be 'word excluding asciiword'.  So by dropping the mapping for 'word', you are excluding words other than asciiwords.  This means dropping not only words that are entirely foreign, but also words with accents.  It sounded like you were OK with either dropping the accent, or dropping the entire word, but maybe not.

But something with a mixture of letters and numbers is not tokenized to either 'word' or 'asciwword', but rather to 'numword', so would not be excluded unless you also dropped 'numword' in addition to 'word'.

If these don't do what you want and you need to use a function, I don't know what you want the function to do precisely, so it is hard to give an example.  If you go that route and implement the function that does what you want and find it not efficient enough, you can start a new post on that topic.


How to read/write from replicated secondary in case primary went down in SQL Server? I've created a transactional publication in SQL Server, now my requirement is to write on primary and read from subscriber and in case of primary going down for some reason, the subscriber should act as a primary automatically.
For now all I have is a publisher that writes to the subscriber.

Transactional replication is not the correct technology for the requirement you've described. What you're after is High Availability/Disaster Recovery (HADR) protection for the SQL Server, and transactional replication is not suitable or intended for this.

Brent Ozar has a High Availability Planning Worksheet in his First Responder toolkit. I would recommend you take a look at that to help you determine your requirements and design an appropriate architecture.

To keep it simple, below are your main options:

AlwaysOn Availability Groups
SQL Failover Cluster Instance
Log Shipping
Database Mirroring
VM/SAN replication

Which option you choose depends on your current environment (version and edition of SQL Server are important), HADR requirements, licensing, budget and in-house skills to design, implement and manage the chosen solution.

For example, if you are running SQL Server 2014 or earlier, you need Enterprise Edition to run Availability Groups, but you can use Database Mirroring (now deprecated).

Running Availability Groups or Failover Cluster Instances requires a Windows Server Failover Cluster installed on the SQL Server nodes to provide HADR - do you have skills in-house to build and manage the cluster?

If your RPO and RTO requirements are not substantial, or you're running an older version of SQL Server, then Log Shipping might be a suitable solution. You can, with some effort, implement a solution to allow log shipping to 'write-back' from the secondary to the principal server after failing over.

Lastly, if you do the discovery work and you don't have the budget for your required RPO/RTO, your infrastructure team might be able to leverage SAN replication and VM DR technology (VMWare's SRM, Azure ASR, Hyper-V Replication etc) to provide protection for your SQL Server environment.

If you can, I would suggest engaging a skilled consultant who can perform the assessment and discovery work to provide an appropriate architecture meeting all your constraints and will be able to help build and deploy the solution. You should also look at training options to get up to speed on HADR technologies so you can adequately manage the system.

Some links to help you:

AlwaysOn Availability Groups
Failover Cluster Instances
Log Shipping
Database Mirroring


Which is better for long term database scalability: adding columns or having key/value store For application, which has few (5-10) settings at the moment but will have a lot more (up to 100) in the future which would be better approach:

to add new columns to settings table every time new setting is introduced
to have settings table as key/value storage

Consider that application might have millions of instances (which all run on the same db and use the same table for settings), DB might be sharded. DB is relational, i.e. either MySQL or T-SQL.

As a developer I would prefer the 2nd variant, so I could scale the application without changing the DB schema and add/remove settings at will. As far as I understand having several million records in a single table shouldn't be an issue if the index is clustered on application instance. Are there any drawbacks that I'm not aware of?

And what about 1st variant? Are there any big benefits? And what about the number of columns: is there any theoretical limit of columns that table can have? What happened if I had a table with 1000 (10 000, 1000 000) columns? Would that be slow?

Option 2 is known as "EAV" or Entity-Attribute-Value

not relational
no DB level constraints
requires contortions to read the data unless a simple list

But, it depends what you mean by "settings". If you have a few 1000 rows that are not objects and don't require constraints then, yes, use this pattern. This is what SQL Server does with sys.configurations

If you are trying to have a "flexible schema" that can store anything, then simply don't. It will end in tears. Also see EAV questions here on DBA.SE

Note that "extra columns" (option 1) allows you to define defaults and datatype-safety, whereas "lack of row" (option 2) requires a default value to be stored in code and everything is a string in the database

"It depends"


Converting Microsoft SQL Server collation to UTF 8 I've a Rails-Application, which reads data from a Microsoft SQL Server 2008 R2 Database.

All umlauts are represented by a '?', so I checked the encoding of the database.  The encoding is 'Latin 1'.   When I try to change it, I have a big list of different collations, but UTF-8 is not listed.  The rails application communicates with the database using an ODBC DSN, could this be the source of the problem?

How can I change the database collation to UTF-8?

Encoding? Do you mean collation? The collation only affects things like equality tests, sort order, etc. They don't affect the allowed encodings for column values.

If you want to store unicode text, you should make sure your columns are among

NCHAR
NVARCHAR
NTEXT (2-byte characters)

rather than

CHAR
VARCHAR
TEXT (1-byte characters).