database is not responding for hours trying to import a 2mb csv with MariaDB I'm trying to import some csv files into my mysql mariadb database, but I'm having problems with some files.
Importing csv with a line with only spaces (2MB of space character) the database stops for hours without giving an answer.
I then created a csv file with only spaces, copying it exactly from one of the many files that cause the problem: 1.888.653 byte of spaces and when I run the query by importing this file the server no longer responds.

MariaDB version: 10.1.31-MariaDB - mariadb.org binary distribution
Protocol version: 10

This is the query I run:

LOAD DATA LOCAL INFILE
'x:/storage/sizetest.txt'
IGNORE
INTO TABLEdatatest
LINES TERMINATED BY "\n"
(@line)
SET
email= REGEXP_SUBSTR(@line, "^[^;:,]*"),
password= REGEXP_SUBSTR(@line, "[^;:,]+$");

This is the server structure:
email: varchar(255) utf8_bin
password: varchar(255) utf8_bin

My.ini configuration:

key_buffer = 64M
max_allowed_packet = 1M
sort_buffer_size = 5M
net_buffer_length = 8M
read_buffer_size = 5M
read_rnd_buffer_size = 5M
myisam_sort_buffer_size = 8M
log_error = "mysql_error.log"
innodb_buffer_pool_size = 2650M
innodb_additional_mem_pool_size = 64M
innodb_log_file_size = 2048M
innodb_log_buffer_size = 512M
bulk_insert_buffer_size = 2048M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 50

Log files do not report any errors while waiting. I waited more than 2 hours and then I stopped loading, and every time I encounter a file like that the server no longer responds. How can I solve this problem?

Either there is a bug, or the system is ferociously swapping.

Do not blindly increase settings.  How much RAM do you have?  It sounds like you have over-committed RAM and are in the throws of heavy swapping.

innodb_buffer_pool_size = 2650M  -- OK if you have at least 6GB of RAM
innodb_log_buffer_size = 512M    -- go back to default
bulk_insert_buffer_size = 2048M  -- go back to default

All of these could be allocated simultaneously, adding up to 7GB, which would be tight in 8GB of RAM.  If you had multiple connections, running, the last one could be allocated multiple times, thereby needing even more RAM.

As a rule, anything named "buffer" or "cache" does not need to be set to huge values.  Large values help, but there are "diminishing returns".  When large values lead to swapping, performance suffers severely.

These may be directly relevant to loading a CSV file:

max_allowed_packet = 1M  -- change to 4M (due to the nature of your data)
net_buffer_length = 8M
read_buffer_size = 5M
bulk_insert_buffer_size = 2048M  -- go back to default (8M)

If you expect 1.8MB of spaces to be put into varchar(255), well, it will be truncated.

Which Engine is the table?


Postgresql single base on multiple HDD partitions Imagine a situation:

I got a Postgres database working on single server on one of HDD partitions (partition size 100GB)
Database grows up to 100GB taking whole free place on the partition
Database stops working because of none free space on HDD

What do I need to do in situation when Postgres database will occupy 100% of partition that the database is on?
Can I move the Postgres database into multiple HDD partitions which there will be similar amount of data on all of them?

If it's possible, how to do that?

I would go for:

   CREATE TABLESPACE any_name LOCATION '/any_empty_dir';
   ALTER TABLE any_tables SET TABLESPACE any_name;

you can do this for a couple of tables to move them to a different filesystem.
it should be pretty easy to do.

you can also put entire databases on to different filesystems.


How to backup an Oracle database from SQL Developer New to Oracle, usually work with SQL Server.

Is there an easy way to backup an Oracle database from SQL Developer.

I've read these links Database Backup and Recovery User's Guide and RMAN and it's a CRAZY involved process!  There's gotta be an easier way, right?

No. And then yes. Well, it depends.

What are your requirements?

Backup / Restore Capabilities

Do you want a backup of the database ...

... for a restore
... to perform recovery
... to roll forward additional archive logs

Then RMAN is your tool of choice. Yes, RMAN is pretty complex and has a steep learning curve. However, once you have mastered it, you will benefit from a large variety of possibilities that RMAN provides you with, e.g. central RMAN catalog for multiple Oracle instances.

Export / Import Capabilities

Do you want a dump of a database ....

... for a "restore" to a new server/instance
... no recovery required
... no additional roll forward of archive logs

...then you might want to consider using the EXPDP (see: Data Pump Export) and IMPDP (see: Data Pump Import). These tools provide you with the ability to obtain schema-specific data and import the data into different tablespaces; or to filter the export via table names, dependencies. On import the schema can then be re-linked to a different schema (see: REMAP_SCHEMA), etc.

SQL Developer

SQL Developer has the capability to export data using a wizard. This is similar to the built-in EXPDP and IMPDP tools provided with the RDBMS installation. A quick guide to using the wizard can be found at: Exporting and Importing Metadata and Data and there in the section Using SQL Developer for Exporting and Importing (Tools | Database Export).


How can I pg_dump my database while excluding extensions? I am trying to migrate my PostgreSQL 9.3 database to heroku.

My database uses the UUID-ossp contrib extension

I am not able to load my db onto Heroku because

ERROR:  permission denied for language c

on

-- Name: uuid_generate_v1(); Type: FUNCTION; Schema: public; Owner: -
--
CREATE FUNCTION uuid_generate_v1() RETURNS uuid
    LANGUAGE c STRICT
    AS '$libdir/uuid-ossp', 'uuid_generate_v1';

I understand I can simply

CREATE EXTENSION "uuid-ossp";

in my database.  But how can i exclude the various UUID functions from my pg_dump in the first place?

It looks like you've installed the ossp-uuid extension from the sql scripts directly, rather than with create extension.

You should CREATE EXTENSION "uuid-ossp" FROM unpackaged; on your local DB, before creating the dump. That'll convert it into an extension, which is dumped as CREATE EXTENSION ... instead of individual CREATE FUNCTION ... commands.


Can SQL Server 2008 R2 extended events execute stored procedure or query? I'm trying to use extended events to track queries that never complete using Event Pairing as a target.  (see this question I asked a few days ago).  The event pairing target should let me find queries that started but never completed.

My new problem is that I need to know what else was happening on the server when this event happens.  I'd like to execute a stored procedure like sp_who, sp_who2, sp_WhoIsActive, etc. whenever I find a query that has not completed in 20+ seconds, for example.  Is this possible?

Out of the box, no.

You'd need to hook into the event stream for extended events and then take action based on that. Tom Stringer has a good overview and sample code to do this!

See also Introducing the Extended Events Reader by Mike Wachal.

Jonathan Kehayias also describes it in detail in his Pluralsight course.


Use insert trigger to copy a modified NEW record to another table, with unknown columns I use a Postgres extension (promscale) that dynamically creates tables with similar schemas: the tables all have a 'time' and a 'value' columns, then a number of labels columns.
Upon insert in any of those tables I need to make store a copy of the new row into a sibling table, after recalculating the value.
This question almost has the answer, but I can't explicitly access sample.value on the anyelement variable.
Here is what I tried:
create or replace function increase_trigger() returns trigger as $update_increase$
declare
    tbl_out varchar;
begin
    tbl_out := TG_TABLE_NAME || ":increase";
    select calc_increase(new, tbl_out);
end;
$update_increase$ language plpgsql;

create or replace procedure calc_increase(sample anyelement, tbl_out regclass) as $$
begin
    sample.value = sample.value + 1.0;
    execute format('insert into %s select $1.*', tbl_out) using sample;
end; $$ language plpgsql;

Of course the parser complains that "sample.value" is not a known variable.
I know the output table name and thus its composite type name, but I don't know how to use it in the function. I found could cast a my input sample (not tested):
execute format("select ROW($1.*)::%I from $1.*", tbl_out) using sample;

... but I can't put everything together to get something that works.
Any help appreciated!
Thanks!
[Edit2] The solution is indeed to use RECORD as a type instead of anyelement:
The complete code is as follows:
create table t1 (
    c1 varchar,
    c2 double precision
);

create table t2 (
    c1 varchar,
    c2 double precision
);

create or replace procedure calc_increase(sample record, tbl_out regclass) as $$
begin
    -- test
    c2.value = c2.value + 1.0;
    execute format('insert into %s select $1.*', tbl_out) using sample;
end; $$ language plpgsql;

create or replace function update_increase() returns trigger as $update_increase$
declare
    increase record;
begin
    call calc_increase(new, 't2');
    return new;
end;
$update_increase$ language plpgsql;

create trigger t_increase before insert on t1 for each row
    execute function update_increase();

insert into t1(c1,c2) values('foo', 10.0);

Looks like the underlying issue is that you're trying to pass the row the trigger is acting on as ANYELEMENT, which is for types; what you want here is RECORD.
Assuming you're using PostgreSQL 11 or later (example contains CALL syntax), this should work:
CREATE OR REPLACE FUNCTION increase_trigger()
  RETURNS TRIGGER
  LANGUAGE plpgsql
AS $$
DECLARE
    tbl_out VARCHAR;
BEGIN
    tbl_out := TG_TABLE_NAME || '_increase';
    CALL calc_increase(new, tbl_out);
    RETURN NEW;
END;
$$;

CREATE OR REPLACE PROCEDURE calc_increase(
   sample RECORD,
   tbl_out REGCLASS
)
  LANGUAGE plpgsql
AS $$
BEGIN
    sample.value = sample.value + 1.0;
    EXECUTE FORMAT('INSERT INTO %s VALUES ($1.*)', tbl_out) USING sample;
END;
$$;

Not tested for potential side-effects.


Connection's MAC address randomly changes on Windows 10, then stops Because reasons (tm), we want to identify the client's MAC address on SQL Server side (local network, not about security, enables a very convenient feature in way transparent for applications).
We do that by querying net_address from master.dbo.sysprocesses. All clients are set up to use TCP/IP connections, the address value has always been correct, and all connections from the same computer have always had the same net_address.

In Windows 10 clients, however, every single connection gets its own net_address, and they do not repeat. The only pattern we can see about these addresses is that the last two digits remain the same within approximately 6 seconds. So if another connection gets assigned with EBC1E384F4C2, all subsequent connections' addresses will also end with C2 until the 6 seconds elapse, then the suffix changes for another 6 seconds.

We have tried in various combinations:

running cliconfg.exe and making sure only TCP/IP is enabled, and that there is an alias for the server name that is also set to use TCP/IP
disabling and enabling back connection pooling in the ODBC Data Sources
adding and removing Network Library=DBMSSOCN or Network=DBMSSOCN to and from the connection properties, depending on the connection type
setting and removing a fixed MAC address in the network adapter properties
removing and adding back TCP/IP v6 support from and to the network connection properties
reading about MAC randomization in Windows 10 which claims it only applies to Wi-Fi connections, and we only have wired connections

Nothing seemed to have any effect.

The only thing that did seem to have effect is:

You toggle the connection pooling status in ODBC Data Sources (does not matter if it was on or off, what matters is that you flip the status to the opposite), but only provided that
The system has been running for at least about 12 minutes after startup. If you flip the pooling status earlier than that, it will seemingly not have any effect.

The moment you do that, the addresses stop changing, but they do not revert to the actual MAC address of the network adapter, instead they freeze at whatever values they had when the freeze happened, and from then on each application will receive the same MAC for all its subsequent connections, but that MAC will be different among different application on the same device. If you close and run an application again, it will receive its frozen MAC address again as if you never closed it.

What is going on? How do we stop it from happening so that TCP/IP connections properly use the network adapter's MAC as opposed to a random address?

Alternatively, is there a better way to reliably identify and distinguish client devices (not users) on a local network from SQL Server (e.g. hostname is no good because it depends on the connection string)?

One of the things you have if you use IPv6 is something called Privacy Extensions. This changes the IP address every hour (or whatever implementation specific time). This guarantees anonymity of the MAC.


SQL Server 2012 compress column for whole table I was wondering if SQL Server supports a kind of dictionary compression for a whole table, and not just a page.

The system I'm working on wasn't initially created for the large amounts of data it processes today. The problem I'm having at the moment is the following:

The application allows users to create legal contracts. These contracts are standardized, however the user is allowed to adjust the contents (text) of the contract if he wishes.

To facilitate this, each contract makes a copy of the standardized contract text. In reality we've discovered that users almost never edit the contract texts so we've ended up with a table with a lot of duplicate data.

Usually I would change the DB model to fit the use case, however this is a legacy system and such a change is pretty expensive. There's being worked on a replacement for it, so investments like these aren't done as easily.

Is it possible to have column dictionary compression for the whole table, and not just 1 page?

We're using SQL Server 2012 in a on premise SQL cluster.

The problem is that the table is 80GB in size, the whole DB is 180GB in size. This table takes up a lot of space and we don't have enough memory so SQL server keeps unloading the data.

The data of this table is used to generate a PDF which represents the contract. Each time a user modifies the state of a contract a new PDF is generated and stored for auditing purposes, this generates a lot of reads on this table.

The reads go down to disk (since SQL server keeps unloading the table from memory). This is creating a huge IO strain on our SAN.

The memory problems are being worked on, but that will take a couple of weeks. It suffices to say for now that simply plugging more memory in isn't an option at the moment.

My thinking is: for a short term solution - compress the data which will drastically reduce the size of the table and thereby making it possible for SQL Server to keep the table in memory which reliefs our IO strain on the SAN.

Is it possible to have column dictionary compression for the whole table, and not just 1 page?

For the situation you are describing SQL Server's built-in compression probably won't do anything at all. I assume that the documents are stored in NVARCHAR(MAX) columns to allow for non-ASCII characters and to allow values longer than 4,000 characters. Off-page values are not compressed by any of SQL Server's methods (row compression, page compression, or the unicode compression that comes with them) so most likely all of your documents won't be touched.

So in short: no there is nothing SQL Server can do automatically to help you here.

Possible Options: (that are a bit more manual and hacky)

If you are using 2016 or later or Azure SQL[1][2] then you could use the COMPRESS/DECOMPRESS functions to store the data as smaller blobs in VARBINARY columns instead. To do this in a way that is transparent to your application(s) you could create a backing table that stores the data, replacing the real table with a view that selects from this and has INSTEAD OF triggers to compress the incoming data. IF you already have a lot of data this could take a long time to roll out. To remove the need for a many hours maintenance window to compress existing content have both NVARCHAR(MAX) and VARBINARY(MAX) columns in the backing table and have the view decide with DocumentText = ISNULL(DECOMPRESS(CompressedText), UncompressedText) and then you can have a process slowly work through the table compressing a batch of rows at a time,
 keeping the batches small enough to avoid locking issues so you can let the process trundle along in production.

Of course if you can affect the application(s) not just the data layer than it would be more efficient to implement the compression of the documents there instead because you save network transfer as well as storage & memory on the database servers.

If a lot of your documents are truly identical then you may see greater gains from deduplication than from compressing each individually, which you can do using similar techniques: store the documents in their own table with a hash of them as a key/index and store a reference to them in the main table, using the backing-table-plus-view-and-trigger method or elsewhere in the application's data manipulation layer. In 2016/Azure (or if using CLR is an option) you can compress the stored documents too for extra space saving.

Another option if many of your documents are almost identical because they are constructed from templates is to store templates and diffs, though this would likely need to be implemented in the application layer or via CLR as I can't imagine doing string diffs and patches in TSQL would be anywhere near efficient enough. This will only work if the documents are stored in an uncompressed form (i.e. RTF files, old office formats, markdown text; not recent MS Office formats or PDFs) because with compressed formats everything is likely to be different after the first change.

These ideas add complexity and result in drops in write performance, so you'd need to judge them with those negatives in mind. I'm playing with the ideas myself for a similar situation where we have a large collection of pickled data (several tens of Gb in total, a few thousand characters average per item) in text format in a legacy application. I'll see if I can get permission to share the results when I'm done.

--

[1] The question was later updated to note that in their case SQL2012 is being used - the deduplication method will still work, and I've left the compression method in the answer as it could be helpful for others with a similar situation. In fact it would be possible in SQL Server 2012 with a custom CLR module to implement the compression, http://aboutsqlserver.com/2015/04/07/compressing-lob-xml-data-in-the-database/ is the first good looking example from a quick search but there are many more out there.

[2] As noted by Paul in the comments below, with 2017 LOB support is coming to compressed columnstore indexes, so that would be another avenue to research for people using a recent enough version of SQL Server.


Worker threads with multiple instances I'm trying to understand the whole subject of worker threads for multiple instances, there's lots of advice on configuring workers but nothing I can find which directly addresses when the server has multiple instances.

Each instance will/can auto configure workers based upon the cpu count, if I have 4 instances for example the theoretical worker count will be 4 times that of a single instance, obviously, but is that actually good .. Am I likely to experience thread starvation? Assume a low cpu count.

I can't publicly discuss the finer details behind the question, however as an example a 4 cpu box would auto configure 512 workers - fine, but with 4 instances the auto config would be 2048 workers ( max )  assuming every instance is busy and gets close to its worker count ( including background workers )  potentially might I expect to have issues?

I have two instances currently installed, not a full database population and a combined worker count of around 400, hopefully that gives the background to my question.

Am I likely to experience thread starvation?  Assume a low cpu count.

Thread starvation doesn't actually have to do with the physical CPU count.  If you have lots of worker threads, and a low CPU count, then it will just be slower to schedule all of those threads on the limited number of CPUs.  See Erik Darling's blog post Max Worker Threads: Don’t Touch That:

The thing is, all changing that setting does is help you not run out of worker threads. It doesn’t make queries run any better or faster. In fact, under load, performance will most likely be downgraded to Absolute Rubbish© either way.
What’s worse? Running out of worker threads and queries having to wait for them to free up, or having way more queries trying to run on the same CPUs? Six of one, half dozen of punching yourself squarely in the nose.

This becomes even more complex in your scenario with multiple instances on the same box ("instance stacking").  Within a single instance, SQL Server uses cooperative scheduling to (try to) make sure that queries share CPU resources in an optimal fashion.
Unfortunately, one instance doesn't know anything about what the other instances are doing.  So each instance will compete for processor time "un-cooperatively" (generally called preemptive scheduling) due to queries running on the other instances.
Is this likely to be a problem for you?  It really depends on how busy the servers are.  But hopefully this answer helps to clarify the situation with regards to worker threads.


Create SQL Server trigger before insert/update I have a table with columns (a, b, c, d) and I would like to ensure that the 4-uplet a,b,c,d is unique for each row of the table by using SQL server triggers.

I basically would like to know how I can use triggers to check that the data that is about to be inserted/updated will not break the property I mentionned above.

So in case of an insert attempt, I would like to execute a SELECT on the table to check if the 4-uplet that's about to be inserted already exist and return an error value in this case. I would like to act similarly for updates.

EDIT: Actually my starting point is that I have an oracle SQL index that I want to convert to SQL Server in the context of a database migration. The Oracle SQL index creation structure is as follows:

CREATE UNIQUE INDEX MY_SCHEMA.MY_UNIQUE_INDEX ON MY_SCHEMA.MY_TABLE
(
    CASE "X_ID" WHEN 2 THEN "COL_A" ELSE NULL END ,
    CASE "X_ID" WHEN 2 THEN "COL_B" ELSE NULL END ,
    CASE "X_ID" WHEN 2 THEN "COL_C" ELSE NULL END ,
    CASE "X_ID" WHEN 2 THEN "COL_D" ELSE NULL END
);

This index seems to impose unicity of (col_a, col_b, col_c, col_d) 4-uplet that's why I chose to translate it by an SQL Server trigger because functional indexes don't exist in SQL Server.

From the discussion in comments, I've restated your requirements:

  When X_ID = 2, then A,B,C,D should be unique. When X_ID <> 2, then no
  uniqueness is necessary.

You can use a filtered index in SQL Server (ie, an index with a WHERE clause).

CREATE TABLE #MyTable (X_ID INT, Col_A VARCHAR(10),Col_B VARCHAR(10),Col_C VARCHAR(10),Col_D VARCHAR(10))

CREATE UNIQUE INDEX MyUniqueIndex ON #MyTable (Col_A,Col_B,Col_C,Col_D) WHERE X_ID = 2;

INSERT INTO #MyTable (X_ID, Col_A,Col_B,Col_C,Col_D)
--non-unique values for x_id = 1
VALUES (1,'A','A','A','A'),
       (1,'A','A','A','A'),
       (1,'A','A','A','A'),
--unique values for x_id = 2
       (2,'A','A','A','A'),
       (2,'A','B','A','A'),
       (2,'A','B','C','A'),
       (2,'A','B','C','D')

--non-unique value for x_id = 2
INSERT INTO #MyTable (X_ID, Col_A,Col_B,Col_C,Col_D)
VALUES (2,'A','A','A','A')

The last insert will give you an error for violating uniqueness:
    Msg 2601, Level 14, State 1, Line 17
    Cannot insert duplicate key row in object 'dbo.#MyTable' with unique index 'MyUniqueIndex'. The duplicate key value is (A, A, A, A).

Depending on whether the columns are NULLable or not, and whether you want NULL to be included in the uniqueness check you may need your CREATE INDEX statement to include checks for NULL. In the above example, SQL Server will treat NULL as a discrete value (which is different than the way Oracle handles NULL). You may instead want to use something like this:

CREATE UNIQUE INDEX MyUniqueIndex ON #MyTable (Col_A,Col_B,Col_C,Col_D)
    WHERE X_ID = 2
    AND Col_A IS NOT NULL
    AND Col_B IS NOT NULL
    AND Col_C IS NOT NULL
    AND Col_D IS NOT NULL;


Connect to SQL Server behind NAT from remote machine The scenario is that this is a SQL Server behind a corporate firewall with your standard setup to allow only minimal outbound traffic and zero inbound.  However, port 80/443 are open, per the usual allowance.

Has anyone tried, or is it possible to

Connect to a remote machine port 80, say using Telnet.
On the local machine, note the bound address and port [1], e.g. netstat
Disconnect from telnet.  Start port forwarder and listen on [1], mapped to SQL Server TCP/IP listener
On remote machine, connect to SQL Server on [1]

What do you think?  I just need to get some testing done and I don't want to move my huge database outside for this one-off.  I am familiar with and have used UDP hole punching for NAT traversal, mainly for push to mobile phones, but am interested to know if this works with TCP as well.

References:

TCP Hole Punching
UDP Hole Punching
TCP Hole Punching #2

I am open to other suggestions.

I doubt that it's easy to do hole punching with TCP or switch the process connected to a port.

Try this for a reverse proxy through the firewall:
http://www.dest-unreach.org/socat/doc/socat-gender.txt

This solutions relies on socat, a swiss-army-knife like network tool which can connect almost any two endpoints and is bidirectional, unlike netcat or the |-pipes in command processors. It should be available for Windows, too.

You can either use socat easily to forward a TCP connection, or in this case: Reverse the "gender" of it: You'll have two socats running, one on each side of the firewall. One continuously connects to the outside. On the outside, socat will wait for a connection from someone else. Only if he connects, the outside socat will accept a connection from the inside socat. Once the inside socat connects to the outside socat, it will connect to the SQL-server. Voila, you can connect from outside to the SQL-server inside, and from the point of the firewall and from the point of TCP the connection looked like it was actually from inside to outside.

To copy from the link:

  1) Start the double client on the inside server
  // every 10 seconds, it tries to establish a connection to the outside host.
  // whenever it succeeds, it forks a sub process that connect to the internal
  // service and starts to transfer data

$ socat -d -d -d -t5 tcp:outside-host:80,forever,intervall=10,fork tcp:localhost:80

  2) Start double server on the outside client
  // wait for a connection from a local client. whenever it accepted it, forks
  // a subprocess that tries to bind to the socket where the inside double
  // client tries to connect (might need to wait for a previous process to
  // release the port)

$ socat -d -d -d tcp-l:80,reuseaddr,bind=127.0.0.1,fork tcp-l:80,bind=outside-host,reuseaddr,retry=10


Is there a need to keep refreshing the indexes from time-to-time? This question is common for all the database products I use, viz. SQL Server, MySQL, Oracle and PostgreSQL.

I have created a fresh database and the tables, at present, have no records. I have created indexes on all such columns of tables which are needed in queries often. I want to know how indexes are physically (clustered) and logically (non-clustered) updated when I keep on adding records in tables? Is the index updated automatically or do I need to update the indexes manually, from time-to-time?

Indexes are automatically updated regarding the what is stored in them (the column values of the rows that are indexed).

However some DBMS require regular maintenance (aka "rebuild") of them in order to optimize the storage of the index values.

Older versions of Microsoft SQL Server required an index rebuild on a regular basis to keep them effecient. But I don't know if that is still true with newer versions (2005,2008)

There is no need to recreate indexes in Oracle.

The (auto)vacuum process of PostgreSQL should take care of reclaiming space, but they sometimes (rarely though) require a rebuild as well.


Build an Oracle query to get parent and child records Table structure as below

col1| col2 | col3|
 1  | empty|empty|
 2  |  1   | Xyz |
 3  |  1   | abc |
 4  |  2   | abc |

Note Col2 is deciding parent record for child record.

Looking to get result if we search for where col3='abc' we will get results as:

1  | empty|empty|
2  |  1   | Xyz |
3  |  1   | abc |
4  |  2   | abc |

Table definition and sample data:

CREATE TABLE ForgeRock
(
    id int,
    Dependency varchar(7),
    Prod varchar(55),
    Prod2 varchar(22)
) ;

INSERT ALL
    INTO ForgeRock (id, Dependency, Prod, Prod2)
        VALUES (1, '', '','')
    INTO ForgeRock (id, Dependency, Prod, Prod2)
        VALUES (2, '1', 'Full','101')
    INTO ForgeRock (id, Dependency, Prod, Prod2)
        VALUES (3, '1', 'Robust','101')
    INTO ForgeRock (id, Dependency, Prod, Prod2)
        VALUES (4, '2', 'Robust','102')
SELECT * FROM dual ;

Such hierarchical queries can be written using CONNECT BY as:

SQL> set null "empty"
SQL> select id, dependency, prod, prod2 from forgerock
     connect by prior dependency = id start with prod2 = '102' order by level desc;

        ID DEPENDE PROD       PROD2
---------- ------- ---------- ----------------------
         1 empty   empty      empty
         2 1       Full       101
         4 2       Robust     102


Very strange tables in mysql own database I was checking some tables into MySQL database, and I can see a lot of tables (1000+) with random names, and I have no idea of how they created and why they exists.

I'm sure they don't belong to the original MySQL database, and the content of the tables is the more weird, just one column named data with datatype LONGBLOB. Some tables have rows in it , some don't.

Here is the query im using and some screnshot of the content of the tables.

Any idea of where this belong and if is secure to drop all of this tables?

SELECT table_name FROM information_schema.tables where table_schema="mysql" and table_name not in ("columns_priv","db","event","func","general_log","help_category","help_keyword,"help_relation","help_topic","host","innodb_index_stats","innodb_table_stats","ndb_binlog_index","plugin","proc","procs_priv","proxies_priv","servers","slave_master_info","slave_relay_log_info","slave_worker_info","slow_log","tables_priv","time_zone","time_zone_leap_second","time_zone_name","time_zone_transition","time_zone_transition_type","user"); /*exclude normal tables*/

So I ended deleting this tables, and luckly everything is working fine , and MySql database is OK again.

No clue yet of why this tables where created , when they were created and how. Also changed all database users to avoid this happen again.

Thanks for the reply!


SQL Server MDW - Query for data collector retention period I have data collectors active on many remote servers and I am trying to find a query that will capture the retention periods for all data collectors active on an instance.

These are system data collectors, part of Management Data Warehouse, not custom collectors.

Any ideas which tables will hold this data?

Inside the database you selected for Management Data Warehouse there is a table called [core].[source_info_internal] that contains a column days_until_expiration

This query gives you the retention periods per collection set:

SELECT [collection_set_uid], [days_until_expiration]
FROM [core].[source_info_internal];

Returns:

+--------------------------------------+-----------------------+
|          collection_set_uid          | days_until_expiration |
+--------------------------------------+-----------------------+
| 2DC02BD6-E230-4C05-8516-4E8C0EF21F95 |                    14 |
| 49268954-4FD4-4EB6-AA04-CD59D9BB5714 |                    14 |
| 3006169E-D33A-4101-A1DE-9F0DABF7D84E |                    30 |
| B83A3832-37CD-4C7A-88F6-6696DA5A7E43 |                    30 |
| 7B191952-8ECF-4E12-AEB2-EF646EF79FEF |                   730 |
+--------------------------------------+-----------------------+

The collection set descriptions are unexpectedly in msdb so if you want the names of the collection set along with the retention period you could use:

SELECT msdb.[name],  mdw.[days_until_expiration]
FROM [core].[source_info_internal]  mdw
INNER JOIN [msdb].[dbo].[syscollector_collection_sets_internal] msdb
on mdw.[collection_set_uid] = msdb.[collection_set_uid];

which returns (on my system):

+---------------------------------+-----------------------+
|              name               | days_until_expiration |
+---------------------------------+-----------------------+
| Disk Usage                      |                   730 |
| Server Activity                 |                    14 |
| Query Statistics                |                    14 |
| Stored Procedure Usage Analysis |                    30 |
| Table Usage Analysis            |                    30 |
+---------------------------------+-----------------------+

Please note however that the Agent job that purges the data calls a stored procedure that accepts parameters so this query lists the default retention periods. If somebody changed it at the job step level you wouldn't get that information.

The procedure definition:

ALTER PROCEDURE [core].[sp_purge_data]
    @retention_days smallint = NULL,
    @instance_name sysname = NULL,
    @collection_set_uid uniqueidentifier = NULL,
    @duration smallint = NULL,
    @delete_batch_size int = 500
AS

So somebody could have overridden the default settings, you could look at the source code for that procedure to follow the entire logic.


SQL Server AlwaysOn High Availability Maintenance Plans I have a SQL Server AlwaysOn High Availability with Primary & Secondary Database Servers

The Availability Group Backup Preferences is set to Primary.

Maintenance Plan is on Primary and does:

Check Database Integrity - Include Indexes, Physical Only
Backup Databases - Full
Backup Databases - Transaction Logs
Rebuild Indexes - Tables and views
Maintenance Cleanup - Delete files older than 2 weeks

No errors are reported in the plan history but I find that only databases that do not participate in the Availability Group are backed up. There seems to be scant resources on maintenance plans on High Availability environments.

Any advice how I can get maintenance plan to work on the databases in the High Availability group?

The best way is to use Ola Hallengren's maintenance plans, because the ability to determine AGs in the maintenance is already "baked in". However, if for some reason you don't want to or can't use his maintenance plans, then you'll need to insert the deterministic function,
If sys.fn_hadr_is_primary_replica ( @dbname ) <> 1
BEGIN
-- If this is not the primary replica, exit (probably without error).
END
-- If this is the primary replica, continue to do the backup.,
to figure out if the database resides in the primary node or not. The example comes from: https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-hadr-is-primary-replica-transact-sql


Dealing with different types in a fact table I'm working with dimensional modelling for the first time, trying to build a data warehouse that pulls in data from an OLTP database, and I'm having some trouble figuring out what to do with this type of scenario. The database tracks donations into a foundation. I'd like to have a fact table such that the "grain" is a single donation, i.e. at the "transaction" level. Donations can come from different types of donors. Let's say for example they come from individuals and companies, although in practice there are actually four relevant categories. The source data has tables for each of these donor types--so there is an individuals table and a companies table. Individuals and companies have very different attributes, and a typical reporting requirement will be to look at aggregate donations for all types, and then do deeper dives into donations by donor type. My first thought was to build a schema like so

(the colour is meaningless btw, I just happened to take the screenshot when the highlighted table was selected)

Since companies and individuals have such different attributes, the idea was to keep them in their own tables and have them be dimensions. (Remember that there are actually 4+ types like this in the real case).

But this is apparently not a good way to go because that means that there will be lots of NULL foreign keys in the fact table. I have read that this is a no-no (although I'm not sure why).

Another option would be to combine the Companies and Users tables into one big Donors table, which might look like this

This eliminates the NULL foreign keys from the fact table at the expense of simplicity in the dimension table. The Donor dimension table is now very wide, and contains a lot of null attributes (again, remember that there are 4+ types of donors that would be represented, each with its own unique attributes).

Finally, there is the option of having a Donor dimension that references Company and Individual tables. Here's how that might look:

Now all the null foreign keys are in the donor dimension table. Obviously, however, this is no longer a star schema.

I've been searching everywhere I can think of to find some general principles for dealing with this type of situation, but I'm coming up short. I'm just wondering if there is a standard way to deal with this since it seems like it ought to be a standard problem in data warehousing.

I would steer clear of the 3 option. It's called a snowflake instead of a star schema. It's a rather advanced solution which can be used when necessary but has it's own drawbacks. As usual refer to Kimball when looking for datawarehouse design tips.

This is what they say in Snowflakes, Outriggers, and Bridges:

  We generally encourage you to handle many-to-one hierarchical
  relationships in a single dimension table rather than snowflaking.
  Snowflakes may appear optimal to an experienced OLTP data modeler, but
  they’re suboptimal for DW/BI query performance. The linked snowflaked
  tables create complexity and confusion for users directly exposed to
  the table structures; even if users are buffered from the tables,
  snowflaking increases complexity

When it comes to 1 or 2, I would say it's up to the reporting requirements and how well the dimension members (the records in the dimension) should be seen as one "axis" to report against. There can be benefits to combining multiple types of records in a single dimension (such as defining hierarchies and attribute relationships to improve performance) but in this case I would lean towards seperate dimensions for different entities.
Again, Kimball explains this in the basics of dimensional modeling but they have more articles on the subject.

  Rule #1: Load detailed atomic data into dimensional structures.

  Rule #2: Structure dimensional models around business processes.

  Rule #8: Make certain that dimension tables use a surrogate key.

When it comes to NULL values in your fact table foreign keys, those should be solved by using surrogate keys which you should use anyway.

I answered a related question here but it basically comes down again to Selecting Default Values for Nulls which explains how you should handle missing data in your dimensions.

  The first scenario where nulls should be avoided is when we encounter
  a null value as a foreign key for a fact table row during the ETL
  process. We must do something in this case because an actual null
  value in a foreign key field of a fact table will violate referential
  integrity;

In some way you can interpret that as "NULLS are a no-no" but it actually means "insert a dummy record such as N/A in your dimension, and use the surrogate key of that record in your fact tables foreign key field"


Analysis Services Project: Data Source per Enviroment I'm trying to configure a multi dimensional analysis service project, so that each developer of a team can configure his/her own data source (oracle).

Changing the deployment target is easy, but how do I specify which data source the project should use per configuration?

You don't see the data source projects in the same dialog but you can actually configure them per environment using the data source editor dialog.

For example I created an SSAS project with only a data source and only a development configuration and this is what it looks like when I open the .dwproj file in notepad:

<Project xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0">
  <ProductVersion>10.50.4033.0</ProductVersion>
  <SchemaVersion>9.0.2.10</SchemaVersion>
  <State></State>
  <Database>
    <Name>Analysis Services Project1.database</Name>
    <FullPath>Analysis Services Project1.database</FullPath>
  </Database>
  <DataSources>
    <ProjectItem>
      <Name>datasource.ds</Name>
      <FullPath>datasource.ds</FullPath>
    </ProjectItem>
  </DataSources>
  <Configurations>
    <Configuration>
      <Name>Development</Name>
      <Options>
        <ConnectionMappings>
          <ConfigurationSetting>
            <Id>datawarehouse/Id>
            <Name>datawarehouse</Name>
            <Value xsi:type="xsd:string">Provider=SQLNCLI10.1;Data Source=mydb;Integrated Security=SSPI;Initial Catalog=mydb</Value>
          </ConfigurationSetting>
        </ConnectionMappings>
        <ConnectionSecurityMappings>
          <ConfigurationSetting>
            <Id>datawarehouse</Id>
            <Name>datawarehouse</Name>
            <Value xsi:type="xsd:int">0</Value>
          </ConfigurationSetting>
        </ConnectionSecurityMappings>
      </Options>
    </Configuration>
  </Configurations>
</Project>

I then added a new environment using the environment dialog and changed the data source while running with my 'production' configuration.

If I then switch back between development and production configuration and view the data source properties by double clicking them in visual studio the data source changes accordingly.

If you set up an entire project you will be surprised at how much of the properties are actually stored in the .dwproj and dwproj.user configurations and all of those will be honored when you switch between configurations.

For example configuring a storage location on one of my cubes adds this:

    <CubeStorageLocations>
      <ConfigurationSetting>
        <Id>New cube</Id>
        <Name>New cube</Name>
        <Value xsi:type="xsd:string">C:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\Log\</Value>
      </ConfigurationSetting>
    </CubeStorageLocations>

I never tried to compile a list of things that are configurable, but if you open those 2 files in notepad you should be able to see all the properties that you can configure per environment.

These are some that I seem to remember as being able to configure:

deployment target edition
error log files for processing errors

EDIT: somebody pointed me to a blog post by @GregGalloway with a list of configurable settings. It's even more than I expected: Analysis Services Project Configurations

List in case the blog post dissapears:

ConnectionMappings
ConnectionProviderMappings
ConnectionSecurityMappings
CubeKeyErrorLogFiles
CubeStorageLocations
DeploymentMode
DeploymentServerEdition
DimensionKeyErrorLogFiles
MeasureGroupKeyErrorLogFiles
MiningStructureKeyErrorLogFiles
OutputPath
PartitionKeyErrorLogFiles
PartitionRemoteServers
PartitionStorageLocations
ProcessingOption
RemovePasswords
ReportActionPaths
ReportActionServers
StartObject
TargetDatabase
TargetServer
TransactionalDeployment
UserIDs
UserPasswords


List the database privileges using psql I'm in the middle of a database server migration and I can't figure (after googling and searching here) how can I list the database privileges (or all the privileges across the server) on PostgreSQL using the psql command line tool?

I'm on Ubuntu 11.04 and my PostgreSQL version is 8.2.x.

postgres=> \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres

The docs on Privileges give an explanation of how to interpret the output. For specific privileges on a table of the current database, use \z myTable.


When try to do Mysqldump Backup from console then getting such as error mysqldump: Got error: 2003: Can't connect to MySQL server on 'localhost' (10061) when trying to connect

when i am tring to do MySQLDump backup from console then getting such as above error.
I am properly configure my MySQL Enterprise workbench and SQLyog ultimate tool. Able to do query and backup through that.

Kindly any one suggest me what i had mistake in system profile configuration of workbench.
I have check out my connection parameter that is ok.

Do i need to configure anything anywhere?

if you are able to start mysql from command line then there should be no error in taking dump as well

check to see if following command works, Either traverse to directory containing binaries or include them in path variable.
You can also include variables to include port,socket etc in your path.

  mysql.exe -uroot -p -P3306

In order to take mysqldump use following,use switches to your conveniences.

  mysqldump.exe -uroot -p -P3306 --all-databases --events >outfile.sql

Give It a try and let us know in case of any error.

Regards and Happy New Year.


Compare phrases in PostgreSQL? How can I compare 2 phrases in PostgreSQL, omitting word-forms? I tried to test Full text features:

select ts_rank(to_tsvector('eat apple'), phraseto_tsquery('eating apples'))

I expected to get rank 100%, but got 0.0991032. Is there any other type of queries to check that "eat apple" completely matches "eating apples"?

ts_rank() is not meant to check for complete equality. It's a simple ranking function that basically just counts the number of matches in a document. The manual:

  Ranks vectors based on the frequency of their matching lexemes.

Consider:

select ts_rank(to_tsvector('eat apple'), phraseto_tsquery('eating apples'))
     , ts_rank(to_tsvector('xxx eat apple yyy'), phraseto_tsquery('eating apples'))
     , ts_rank(to_tsvector('eat apple eat apple'), phraseto_tsquery('eating apples'))
     , ts_rank(to_tsvector('eat apple eat apple eat apple'), phraseto_tsquery('eating apples'))
     , ts_rank(to_tsvector('eat apple eat apple eat apple eat apple eat apple eat apple eat apple eat apple eat apple'), phraseto_tsquery('eating apples'));

The rank is highest for the last expression just because there are many matches. The first two share the same rank.

  Is there any other type of queries to check that "eat apple" completely matches "eating apples"?

You could compare two tsvecor values for equality:

SELECT to_tsvector('eat apple') = to_tsvector('eating apples');

To be sure (and not depend on current locale settings), spell out the text search configuration:

SELECT to_tsvector('english', 'eat apple') = to_tsvector('english', 'eating apples')

Returns true for an exact match.
Not supported by a text search indexes, though, which only support the operators @@, @@@, <@, and @>. You could combine both. Like:

SELECT * FROM tbl
WHERE  to_tsvector(text_field) @@ phraseto_tsquery('eating apples')  -- with index support
ORDER  BY to_tsvector(text_field) <> to_tsvector('eating apples')  -- exact match first
        , ts_rank(to_tsvector(text_field), phraseto_tsquery('eating apples')) DESC

Sorts exact matches first because FALSE sorts before TRUE.


How change space only columns to null I have a database with four tables that has a number of columns (VARCHAR) that contains one or more spaces. I would like to replace these spaces with NULL.

I can do it if I specify the table and each column

UPDATE LaserData
SET Kant = NULL
WHERE Kant= ''

But is there a way that I can use a list of tables and a list of columns and do this for all without writing a query for each?

That's an odd question, but you could go with something like this:

USE YourDatabaseName
GO

CREATE TABLE #TempColumns (
    id int,
    tname varchar(50),
    cname varchar(50))

INSERT INTO #TempColumns (
    id,
    tname,
    cname)
SELECT
    ROW_NUMBER() OVER (ORDER BY t.name) AS ColId,
    t.name,
    c.name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID

DECLARE @ColumnCount INT =
(
    SELECT COUNT(*) FROM #TempColumns
),
@i INT = 1,
@TableName VARCHAR(MAX),
@ColumnName VARCHAR(MAX),
@DataType VARCHAR(20),
@AllowNull INT;

WHILE (@i <= @ColumnCount)
BEGIN
    SET @TableName = (
        SELECT t.tname
        FROM #TempColumns t
        WHERE t.id = @i);
    SET @ColumnName = (
        SELECT t.cname
        FROM #TempColumns t
        WHERE t.id = @i);
    SET @DataType = (
        SELECT t.name
        FROM sys.columns AS c INNER JOIN sys.types AS t ON c.user_type_id=t.user_type_id
        WHERE OBJECT_NAME(c.OBJECT_ID) = @TableName AND c.name = @ColumnName);
    SET @AllowNull = (
        SELECT COLUMNPROPERTY(OBJECT_ID(@TableName), @ColumnName, 'AllowsNull'));

    IF (@DataType = 'varchar')
    BEGIN
        IF (@AllowNull = 1)
        BEGIN
            EXEC('UPDATE ' + @TableName + ' SET ' + @ColumnName + ' = NULL WHERE ' + @ColumnName + '= ''''')
        END
    END
    SET @i = @i + 1;
END

This will create a temporary table to store all the table names and their respective columns, it will then perform an update (in this case) per every column on every table only when datatype is VARCHAR.


How do I replicate a database over 2 geographical sites? Disclaimer: I have no idea what I'm talking about here, or if what I'm doing is the correct thing to do.

Long story short, my superior has asked me if it's possible to replicate our internal database on another SQL Server instance in another geographical site and keep them both in sync.

For context, they've recently expanded the business and have opened a new building (we'll call this Site B) about 15 miles away from the main building (Site A). Both sites are connected by a VPN.

At Site A, we have a MSSQL 2012 Standard install which has our internal database.

When users from site B are connecting to the database over the VPN, they're reporting that it takes nearly 5 minutes to establish a connection and open the client application. I'll be checking this tomorrow since I've never heard this problem reported before.

My superior seems to think the best idea would be to somehow replicate/mirror (I'm not sure of the terminology here) the database on the server in Site B and somehow keep them both in sync.

Is this what replication is for, and if so, where can I start learning how to replicate the database.

Otherwise, if this is a very bad idea, what other options do I have?

tl;dr - Can I have two copies of a database, kept in sync, in two different buildings?

Based on the answers in the comment section, you are looking at a bidirectional transactional replication.

Microsoft document its implementation at this location:
https://support.microsoft.com/en-us/kb/820675

Using the name "Bidirectional Transactional Replication" in your research should help you identify how-tos and step by step tutorials.

Since it's bidirectional replication, you will experience conflicts (a row updated here and there at the same time).

The document explains all of that very well. It's a good start.


Choosing between NoSQL and Relational databases while having both flexibility and consistency requirements I've never worked with databases professionally and only toyed with database coding using a small sqlite db for my own music collection. Now I'm asked to build an internal database system at work for multiple users. After basic reading of NoSQL vs. relational databases, I'd like to have some ideas on how to choose the right technology.
Context

Before this database, the users work with spreadsheets entirely.
Those sheets differ in structure (schema) from project to project.
Sometimes the same project has several different sheets that need to be isolated but still exist in the same database.
We want to import all those sheets into a database and with new fields added and old fields names/values updated on the fly without breaking the original spreadsheets
When source control ops happen, we want to be able to connect to the database and update certain fields regarding the source control management status
The database will be used by various users from different locations, all through the in-house network for now. There won't be a lot of users like on Facebook or Twitter.
All the data must be localized into multiple languages and ideally co-exist in the same database.

Questions

Since we'll have different sheet structures, is NoSQL a better option than a relational database?
The users will go on using spreadsheets and may change their sheet structure on the fly. Will this be a lot of trouble for NoSQL or relational databases such as SQLite?
NoSQL does not support the ACID of relational databases, does it mean that data may get corrupted when multiple users work on the same record? We normally would like consistency.
If we maintain an optimized database but still want to be able to export selected portions or entire as CSV or JSON files? Will NoSQL or relational work better?
How to handle access control from scratch if we do it online? Will this affect the choice of database tech?
For this type of system, should we hire a database expert dev and/or maintainer? Can we build on some free/open-source infrastructure that requires the least amount of backend/frontend, security tech R&D?

Thanks for your input in advance!

To answer your questions directly:

"Since we'll have different sheet structures, is NoSQL a better option than a relational database?"
A. Probably not, unless each of those sheet structures are very tightly related as one application that they'd all be stored in the same few tables. Otherwise it sounds like you just have a series of structured datasets (multiple applications) that can be represented each with their own different set of tables. This is quite normal and can easily be implemented in either a Relational Database Management System (RDBMS) or a NoSQL database, so no differentiation here.

"The users will go on using spreadsheets and may change their sheet structure on the fly. Will this be a lot of trouble for NoSQL or relational databases such as SQLite?"
A. Are the spreadsheets consumers of the data that was saved down to the database, i.e. besides writing to the database do the spreadsheets also need to read back from the database? If so, then changing structures is something you'll need to handle regardless of which type of database you use, the problem just occurs at different layers of the application. With a RDBMS you need a process to handle managing schema changes in the database that have occurred at the client, otherwise your database won't be storing any new data that is entered. With a NoSQL database, your problem exists in the other direction, when pulling data back into the client's new schema but the instance of data from NoSQL is still out of date and doesn't match the client. With Excel, it may actually be more forgiving though and just leave those columns blank. But keep in mind Consistency is also different in NoSQL should you choose to distribute the data across multiple nodes. It is eventually consistent, which means one user of a spreadsheet may receive a different version of the schema vs another user of that same spreadsheet, if the data from the newer structure hasn't been replicated to the other node yet. More of this on the answer to your next question.

"NoSQL does not support the ACID of relational databases, does it mean that data may get corrupted when multiple users work on the same record? We normally would like consistency."
A. NoSQL follows the BASE principles (and is bound by the CAP theorem), which means unlike a RDBMS that follows the ACID principals, it is eventually consistent. This means a change to the database, including the change of the data itself, is not guaranteed to instantly be replicated across all nodes of which that database is distributed across. But it will be eventually replicated and consistent across all nodes. In regards to your question, that does not mean data is any more likely to be corrupted (from the system's perspective) if you use a NoSQL database vs a RDBMS and multiple users are changing the same record. Rather, it just means that the same record could be in multiple different states at the same time between different nodes, until the last in (generally) change becomes synchronized across all nodes of that database so that it becomes eventually consistent. A RDBMS generally employs an algorithm with a locking mechanism to ensure that consistency is immediate and that the same record is never in more than one state at a time.

"If we maintain an optimized database but still want to be able to export selected portions or entire as CSV or JSON files? Will NoSQL or relational work better?"
A. At a high level, I don't believe you'll find any differences here between either. It's an equally achievable goal for both types of database systems, and the differences will be very low level detail specific as you're getting into it. Some RDBMS natively support storing and/or exporting the data as JSON and CSV formats, and typically NoSQL databases already store (or retrieve) data as JSON and there should be ways to convert the results to CSV with modern implementations of those databases.

"How to handle access control from scratch if we do it online? Will this affect the choice of database tech?"
A. By "doing it online" do you mean a cloud-based solution? If so, this shouldn't change anything from a security standpoint. Either way, all modern database systems have a way to setup accounts either directly in the database system itself or mapped to another security system. For example, the Microsoft SQL Server database system offers the ability to either create dedicated Logins to the database or leverage Active Directory such that you can define access control within the database on top of Windows Users and Groups, and this still functions the same even when using Azure (Microsoft's cloud solution) to host your database. Similar ideas apply to other modern database systems too.

"For this type of system, should we hire a database expert dev and/or maintainer? Can we build on some free/open-source infrastructure that requires the least amount of backend/frontend, security tech R&D?"
A. You sound like you have a lot of questions and thinking to do around the problems you're planning to solve, so if it's possible, hiring someone with strong database experience (especially one who has worked with both RDBMS and NoSQL) would be only of a benefit to you. Currently with the information you provided and based on the questions you've asked, I don't objectively see any distinguishing factor to choose one type of database system over another. My only subjective opinion would be to go with a RDBMS since it sounds like your data is structured at least (even if it does change - that's fine in a RDBMS) and if the eventual consistency of NoSQL can be an issue for your use-cases. If you're looking for an open source and free RDBMS then I'd recommend looking into PostgreSQL.


Can I get shell access through SQL Server 2017 on Linux? There is a stored proc called xp_cmdshell that is supposed to give you shell access on SQL Server, to the server. I am wondering if that works on Linux?

No sadly it doesn't, when I try EXEC xp_cmdshell 'dir *.exe'; I get,

  Msg 15281, Level 16, State 1, Server x230, Procedure xp_cmdshell, Line 1 SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', search for 'xp_cmdshell' in SQL Server Books Online.

When I try to enable it with sp_configure 'xp_cmdshell', 1; I get

  Configuration option 'xp_cmdshell' changed from 0 to 1. Run the RECONFIGURE statement to install.

But, If I retry the EXEC xp_cmdshell command above I get,

Msg 16202, Level 14, State 200, Server x230, Procedure xp_cmdshell, Line 1
Keyword or statement option 'xp_cmdshell' is not supported on the 'Linux' platform.

They are such kill joys.


Very complicated query is suddenly taking 35 minutes to finish in SQL Server 2014 I have a very complicated query that produces correct results slowly but ok in SQL Server 2012. After upgrade to SQL Server 2014 and after setting compatibility mode to 2014 (120) for the database, it is suddenly taking 35 minutes to finish. I have uploaded the actual plan on SQL Server 2014.

SQL Server 2014 with compatibility level 120 uses the new CE. SQL Server 2012 uses the legacy CE. The new CE uses a fundamentally different model for cardinality estimation that is expected by Microsoft to result in better performing queries for most workloads. However, they acknowledge that some workloads or queries may perform worse with the new CE. It is possible that is the situation that you're in.

When investigating a query that isn't finishing quickly enough, one of the first things I try to determine is why the query is slow. Your query is slow because over four billion rows are read from a single index seek operator:

You can verify that this is the most expensive part of the query by looking at the actual time statistics shown at the operator level. The index seek uses 222360 ms of CPU time and the overall query uses 224382 ms of CPU time. The most straightforward way to get better performance is by improving the index on the RSTReclassedJournalEntries table. This table only has 127787 rows, so it's unlikely that the additional maintenance required for a better index would be a concern. Creating an index with key columns Posted and ReclassedJRNENTRY should suffice.

The IX_RSTReclassedJournalEntries_1 index already exists on the table and is a covering index, but you want a index that can filter on Posted and ReclassedJRNENTRY as seek predicates instead of as filter predicates. With such an index SQL Server will read a maximum of 34646 rows from the index instead of 4382443766 rows. This will dramatically improve performance.

The index change that I'm recommending addresses the symptom of the problem instead of the root cause, but sometimes that's enough to meet the business need. The root cause appears to be a join cardinality estimation problem with [SOLMQ].[dbo].[AAG30000].[AK2AAG30000]. Addressing that is likely to be more difficult than making the suggested index change, although it's possible that you'll get lucky and updating statistics on that table will fix the problem as well.


Why should I install both 32 and 64 bits Oracle provider for the same machine? I needed to read an Oracle DB from SQL Server, so I had to install the Oracle provider (ORAOLEDB.Oracle).

My machine:

System: Windows Server R2 2008 Enterprise, 64 bits (SP1).

RDMS: Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)

However, at the beginning I installed just the 64 bits ODAC version for Oracle provider but it did not work. Finally I got it done by installing the 32 bits version, just how is said here.

Then I need to replicate the installation in production, I don't want to install both if is not required, so, I want to know why I should use both.

As alluded to in the question you linked to, some SQL Server tools/applications are 32-bit only. The main SQL Server application (sqlservr.exe) is 64-bit (32-bit may still be available as well, not certain about that for the latest versions); however, some of the supporting tools, perhaps most notable SQL Server Management Studio (ssms.exe), are only available as 32-bit applications.

If you want to install both versions, this should be sufficient reason.

If you want to find out if you can avoid installing the 64-bit version, all I can suggest is uninstalling that version from your development environment, and confirming whether or not everything still works as expected (including SSMS, and any other applications you use). If everything works fine, you can probably skip installing the 64-bit version in production. If you have problems, assume you need both versions.


SSMS connection string In SSMS we are giving hostname\instance name to connect to SQL Instance, can someone please explain me how it is getting connected, is it using any files or protocols or what exactly is happening in back ground so that it gets connected?

Connection to named instances is handled by SQL Browser Service:

  When an instance of SQL Server starts, if the TCP/IP or VIA protocols
  are enabled for SQL Server, the server is assigned a TCP/IP port. If
  the named pipes protocol is enabled, SQL Server listens on a specific
  named pipe. This port, or "pipe," is used by that specific instance to
  exchange data with client applications. During installation, TCP port
  1433 and pipe \sql\query are assigned to the default instance, but
  those can be changed later by the server administrator using SQL
  Server Configuration Manager. Because only one instance of SQL Server
  can use a port or pipe, different port numbers and pipe names are
  assigned for named instances, including SQL Server Express. By
  default, when enabled, both named instances and SQL Server Express are
  configured to use dynamic ports, that is, an available port is
  assigned when SQL Server starts. If you want, a specific port can be
  assigned to an instance of SQL Server. When connecting, clients can
  specify a specific port; but if the port is dynamically assigned, the
  port number can change anytime SQL Server is restarted, so the correct
  port number is unknown to the client.

  Upon startup, SQL Server Browser starts and claims UDP port 1434. SQL
  Server Browser reads the registry, identifies all instances of SQL
  Server on the computer, and notes the ports and named pipes that they
  use. When a server has two or more network cards, SQL Server Browser
  returns the first enabled port it encounters for SQL Server. SQL
  Server Browser support ipv6 and ipv4.

  When SQL Server clients request SQL Server resources, the client
  network library sends a UDP message to the server using port 1434. SQL
  Server Browser responds with the TCP/IP port or named pipe of the
  requested instance. The network library on the client application then
  completes the connection by sending a request to the server using the
  port or named pipe of the desired instance.


PostgreSQL - cannot create RULE from stored procedure I'm trying to automate boring database work. I would like to have one function which creates views, rules, etc so I don't need to run corresponding SQL queries from file. Currently I faced an issue when creating rule from function implemented in PL/pgSQL.

Here is an excerpt from SQL script for rolling out changes:

CREATE TABLE person_x (
  firstname TEXT NOT NULL,
  lastname  TEXT
);

CREATE TABLE person_y (
  firstname TEXT NOT NULL,
  lastname  TEXT
);

CREATE OR REPLACE FUNCTION test_create_rule_from_stored_procedure()
  RETURNS INTEGER AS $test_create_rule_from_stored_procedure$
BEGIN
  CREATE OR REPLACE RULE person_ins AS ON INSERT TO person_x
  DO INSTEAD
    INSERT INTO person_y (
      firstname,
      lastname)
    VALUES (
      new.firstname,
      new.lastname
    );

  RETURN 1;
END;
$test_create_rule_from_stored_procedure$ LANGUAGE plpgsql;

But I get:

psql:rollout.sql:348: ERROR:  42601: syntax error at "person_y"
DETAIL:  Expected record variable, row variable, or list of scalar variables following INTO.
CONTEXT:  compile of PL/pgSQL function "test_create_rule_from_stored_procedure" near line 4
LOCATION:  read_into_target, gram.y:2322

But when I run query for creating rule (the query is the same as in function) directly from SQL script everything is fine.

Is it possible to create rules from stored procedures? Or I need to compose query for creating rule as string and then execute it with aid of EXECUTE?

PS I use PostgreSQL v. 8.3 and I cannot upgrade.

Thank you everyone for suggestions in comments. I've found out two solutions.

Use SQL stored procedure instead of PL/pgSQL:

    CREATE OR REPLACE FUNCTION test_create_rule_from_stored_procedure()
      RETURNS VOID AS $test_create_rule_from_stored_procedure$
    CREATE OR REPLACE RULE person_ins AS ON INSERT TO person_x
    DO INSTEAD
      INSERT INTO person_y (
        firstname,
        lastname)
      VALUES (
        new.firstname,
        new.lastname
      );
    $test_create_rule_from_stored_procedure$ LANGUAGE SQL;

Stay with PL/pgSQL but specify query for creating rule as string literal:

    CREATE OR REPLACE FUNCTION test_create_rule_from_stored_procedure()
      RETURNS INTEGER AS $test_create_rule_from_stored_procedure$
    BEGIN
      EXECUTE 'CREATE OR REPLACE RULE person_ins AS ON INSERT TO person_x
      DO INSTEAD
        INSERT INTO person_y (
          firstname,
          lastname)
        VALUES (
          new.firstname,
          new.lastname
        );';

      RETURN 1;
    END;
    $test_create_rule_from_stored_procedure$ LANGUAGE plpgsql;


No advantage of using Cross Apply or CTE over inline sub-query I came across a query like this:

SELECT (SELECT COUNT(1) FROM Orders o WHERE i.ItemId = o.ItemId) [C]
FROM Items i

I changed it to following

;WITH cte_count
AS
(
    SELECT COUNT(1) c, OrderId FROM Orders Group By ItemId
)
SELECT a.c [Count], i.Name
FROM Items i
INNER JOIN cte_count c ON (c.ItemId = i.ItemId)

But the execution plan for both is same as shown below:

Similarly there was another query selecting TOP 1 Order By Id. I tried moving this one to CROSS APPLY But for this one as well I got same execution plans.

Of course there were other joins and columns in query.

My dilemma is about utility and advantages of using CROSS APPLY and CTE. Are there any or its just exotic?

But the execution plan for both is same as shown below:

The plans are different. One is an inner join, the other is an outer join. The results may be the same in your simple test, but the semantics are different. In more complex queries, the difference may cause more obviously different execution plans, and come with a performance impact.

There are usually many ways to express the same requirement (or similar requirement, as in your example) in SQL. Which you use is initially a question of preference and style. In some cases, using one or the other will produce important performance differences because the declarative SQL takes a different code path through the optimizer. In this particular case, the outer join may play less well with the optimizer's exploration abilities (it has fewer tools to use with outer joins than inner joins).

Rewriting a query to define the same results using different syntax can be a valid tuning method, but it requires careful attention to detail and retesting whenever SQL Server is patched or upgraded. There is generally no reason to prefer one way of expressing a requirement in SQL over another.

In addition, as Andriy mentioned in a comment on the other copy of your question, "in the more general case, your inline queries will give you only one result per row, while joining to a CTE (which doesn't have to be a CTE, it could be a normal subselect) or CROSS APPLYing a row set can give you access to more than one column."


Do I need to index a key that I partition by? I have an InnoDB table of customers, partitioned by KEY, with each partition being one US state.  I have an index on phone number, and the performance is great.  We also need to SELECT by last name in a given US state.  Should I create an index on lastname AND state, or there's no point in doing that because the table is already partitioned by state?

Will that take up unnecessary space, since InnoDB should already know what partition is which state, and it only has to find all rows matching last name in that partition?

We aren't doing any joins whatsoever...this is just for a simple query like this:

SELECT * FROM table WHERE lastname = "Smith" AND state="NY"

You should create an index that involves lastname and maybe state,lastname.
Think of it. What would obligate a Query Optimizer to plan a query to search by lastname within a partition? The only course of action would be a full table scan within the correct partition.
Let's assume 50 U.S. States (no Puerto Rico, Guam, US Commonwealths, or Protectorates)
If you have 1 million rows, that would be average out 20,000 rows per state. A table scan of 20,000s for a lastname may not be that bad. However, we are talking about the United States. Huge numbers could live in New York, California, Texas and Florida. Smaller numbers will reside in Rhode Island, Idaho, Wyoming, and Montana. Search times via full table scans will vary greatly. In light of this, it would be beneficial to index names within each state.
I suggest two indexes

LastName : The second index will get the Query Optimizer to search for LastName in every partition. Doing 50 index lookups would sure beat a 50 full table scans any given day. You mentioned you have an index on phonenumber only and the performance was great. The same would have to apply to a LastName-only index.
State,LastName : The first index will get the Query Optimizer to search for LastName only down one partition, the state you selected (Maybe unnecessary if the Query Optimizer is intelligent enough to examine the query and select the correct partition).

UPDATE 2012-10-09 16:21 EDT
In all fairness to MySQL's partitioning, if the Query Optimizer is smart enough to navigate data retrieval from just one partition base on the WHERE clause, then indexing (state,lastname) may be unnecesaary. Nevertheless, you must still index on lastname. That way, each partition is indexing on lastname.
My original suggestion of indexing on (state,lastname) is actually based on how I used to index MyISAM tables that were combined under the MERGE storage engine.
Here were my past examples on MERGE tables:

Jan 04, 2012 : get column from too many tables in mysql
Jan 15, 2012 : Separate tables or partition one huge table?

Since Table Partitioning is implemented, indexing on lastname should suffice for you. In fact, recall you stated that the phonenumber is indexed and performance was great, So, I say for the second time, indexing lastname should also be as good for you.


MySQL Index Fragmentation Is there any way to find out the fragmentation of indexes in MySQL ?

If such an output can be obtained, it would be helpful to identify which all indexes need to be rebuilt.

I'm using MySQL 5.1 on Windows Server 2008

Can somebody please help me.

For InnoDB tables, this is both possible and redundant.
It is possible using Percona Server's INNODB_INDEX_STATS table in INFORMATION_SCHEMA. This table shows you the number of pages per index in an InnoDB tables.

Well, apparently InnoDB manages its own pages very well. Upon purging of data it merges back its index nodes. I have done multiple tests to verify that an OPTIMIZE TABLE -- a complete rebuild of the table -- does not actually improve on index fragmentation.

Please see my post on the subject.

By the way, I believe MySQL 5.6 has a similar table built-in.

With regard MyISAM tables, I'm unaware that there's any way of looking at the fragmentation.


NEWID() In Joined Virtual Table Causes Unintended Cross Apply Behavior My actual work query was an inner join, but this simple example with cross join seems to nearly always reproduce the problem.

SELECT *
FROM (
    SELECT 1 UNION ALL
    SELECT 2
) AA ( A )
CROSS JOIN (
    SELECT NEWID() TEST_ID
) BB ( B )

With my inner join I had many rows for which I added to each a GUID using the NEWID() function, and for about 9 out of 10 such rows the multiplication with the 2-row virtual table produced the expected results, just 2 copies of the same GUID, while 1 out of 10 would produce different results. This was unexpected to say the least and gave me a really hard time trying to find this bug in my test data generation script.

If you take a look at the following queries using as well non-deterministic getdate and sysdatetime functions, you won't see this, I don't anyway-I always see the same datetime value in both final result rows.

SELECT *
FROM (
    SELECT 1 UNION ALL
    SELECT 2
) AA ( A )
CROSS JOIN (
    SELECT GETDATE() TEST_ID
) BB ( B )

SELECT *
FROM (
    SELECT 1 UNION ALL
    SELECT 2
) AA ( A )
CROSS JOIN (
    SELECT SYSDATETIME() TEST_ID
) BB ( B )

I'm currently using SQL Server 2008 and my work around for now is to load my rows with GUIDs into a table variable before finishing out my random data generation script. Once I have them as values in a table as opposed to virtual table, the problem goes away.

I have a workaround, but I'm looking for the ways to workaround without actual tables or table variables.

While writing this I tried without success these possibilities:
1) placing the newid() into a nested virtual table:

SELECT *
FROM (
    SELECT 1 UNION ALL
    SELECT 2
) AA ( A )
CROSS JOIN (
    SELECT TEST_ID
    FROM (
        SELECT NEWID() TEST_ID
    ) TT
) BB ( B )

2) wrapping the newid() within a cast expression such as:

SELECT CAST(NEWID() AS VARCHAR(100)) TEST_ID

3) reversing the order of appearance of the virtual tables within the join expression

SELECT *
FROM (
    SELECT NEWID() TEST_ID
) BB ( B )
CROSS JOIN (
    SELECT 1 UNION ALL
    SELECT 2
) AA ( A )

4) using uncorrelated cross apply

SELECT *
FROM (
    SELECT NEWID() TEST_ID
) BB ( B )
CROSS APPLY (
    SELECT 1 UNION ALL
    SELECT 2
) AA ( A )

Just before finally posting this question, now I tried this with success it seems, a correlated cross apply:

SELECT *
FROM (
    SELECT NEWID() TEST_ID
) BB ( B )
CROSS APPLY (
    SELECT A
    FROM (
        SELECT 1 UNION ALL
        SELECT 2
    ) TT ( A )
    WHERE BB.B IS NOT NULL
) AA ( A )

Anyone have any other more elegant, simpler workaround? I really don't want to use cross apply or correlation for a simple row multiplication if I don't have to.

This behaviour is by design, as explained in detail on this Connect bug report.  The most pertinent Microsoft reply is reproduced below for convenience (and in case the link dies at some point):

  Posted by Microsoft on 7/7/2008 at 9:27 AM

  Closing the loop . . . I've discussed this question with the Dev team.
  And eventually we have decided not to change current behavior, for the
  following reasons:

  The optimizer does not guarantee timing or number of executions of
  scalar functions. This is a long-estabilished tenet. It's the
  fundamental 'leeway' tha allows the optimizer enough freedom to gain
  significant improvements in query-plan execution.
  This "once-per-row behavior" is not a new issue, although it's not
  widely discussed. We started to tweak its behavior back in the Yukon
  release. But it's quite hard to pin down precisely, in all cases,
  exactly what it means! For example, does it a apply to interim rows
  calculated 'on the way' to the final result? - in which case it
  clearly depends on the plan chosen. Or does it apply only to the rows
  that will eventually appear in the completed result? - there's a nasty
  recursion going on here, as I'm sure you'll agree!
  As I mentioned earlier, we default to "optimize performance" -
  which is good for 99% of cases. The 1% of cases where it might change
  results are fairly easy to spot - side-effecting 'functions' such as
  NEWID - and easy to 'fix' (trading perf, as a consequence). This
  default to "optimize performance" again, is long-established, and
  accepted. (Yes, it's not the stance chosen by compilers for
  conventional programming languages, but so be it).

  So, our recommendations are:

  Avoid reliance on non-guaranteed timing and number-of-executions
  semantics.
  Avoid using NEWID() deep in table expressions.
  Use
  OPTION to force a particular behavior (trading perf)

  Hope this explanation helps clarify our reasons for closing this bug
  as "won't fix".

The GETDATE and SYSDATETIME functions are indeed non-deterministic, but they are treated as runtime constants for a particular query.  Broadly, this means the function's value is cached when query execution starts, and the result re-used for all references within the query.

None of the 'workarounds' in the question are safe; there is no guarantee the behaviour will not change the next time the plan is compiled, when you next apply a service pack or cumulative update...or for other reasons.

The only safe solution is to use a temporary object of some kind - a variable, table, or multi-statement function for example. Using a workaround that appears to work today based on observation is a great way to experience unexpected behaviours in future, typically in the form of a paging alert at 3am on Sunday morning.


Is it normal that our Wordpress' database crashed multiple times in this month alone? I just started a new job at a small magazine as an IT guy. It all worked fine, but I'm worried about some issues with our VPS installation.

Since I started working here (26 days ago), our Wordpress' databases crashed more than once. When I check with our host, they tell me about possible server overload. I am not convinced.

Actually, it did just happen again, even though we are experiencing one of those slow-news days, and about 30 people were on the site when the crash occurred.

To be honest, I worked a lot with Wordpress and this never happened to me, let alone at this frequency. My colleagues told me that this is how things were always going.

Is this normal? Should we be looking for another contractor?

EDIT — some technical details on our setup

The technical details are a bit problematic: the company hired a third-party contractor to manage all the hosting stuff. Those guys refuse to cooperate with me, resisting even giving me information I requested and proper access to log files.

But what I know is that it's a VPS running under Ubuntu with NginX and MySQL (no details on the versions used.).

When I asked for log files related to the last crash, I got a long list of this :

160226 12:56:35 [ERROR] /usr/sbin/mysqld: Table '<table_name>' is marked as crashed and should be repaired
160226 12:56:35 [Warning] Checking table:   '<table_name>'

Don't ever use MyISAM. Ever. Under any circumstances. Just don't. If you need its performance characteristics for some reason, use MariaDB with Aria tables.

MyISAM is not crash safe, and will corrupt data when it breaks.

Also, my webserver with WordPress has over 5k posts and has never crashed its db.

This is not normal behavior.


Filter a SQL replication I'm trying to replicate a SQL database but I want to apply a filter based on date.
The database has more than 6000 tables and all of them have a datetime column (stamp) with the same name "usrdate".
Is it possible to apply a filter to be applied to all columns?
The goal is to have multiple databases in the replication site with data between periods.
Thanks

Assuming you mean Microsoft SQL Server, yes you can apply a Filter on the Publication using that usrdate column on each Article you're replicating, but you'll need to add the Filter for each replicated article. Also there are some limitations (mentioned in the previously linked docs) and using Publication Filters aren't applied like regular SQL predicates so they may be less performant than doing it in SQL yourself.
You may be best off creating a series of Views on top of the Tables you want to replicate that apply the date filter as a predicate in SQL, which you probably can more easily script out than trying to add the Publication Filter to each Article.


SQL Server: Does the sysadmin role encompass all the other roles? When creating a new login on SQL Server you have to explicitly add server roles to that user. You have the option of checking the 'sysadmin' role along with all the other roles. Is there any point to this?

I.e. if you add the 'sysadmin' role to a user surely that encompasses all the other roles?

You do not need to add any server roles to a new user, those are optional (although it is best to leave the public role checked).

The sysadmin role encompasses all other roles, and is deity level access within SQL Server, granting you the rights and permissions to do whatever you want with the instance.


MySQL insert operation hang while another update operation is running, but can't figure out why Transaction 1:

begin;
update t_info set gender=1 where c_score > 85;

Transaction 2:

begin;
insert into t_info(c_id,c_class_no,c_score,c_name,gender) values (4,99,50,'gap',1);

Transaction 2 hangs, and I can't figure out why.

Preconditions: mysql Ver 14.14 Distrib 5.7.19-17 tx_isolation: REPEATABLE-READ

DROP TABLE IF EXISTS `t_info`;
CREATE TABLE `t_info` (
  `c_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  `c_class_no` int(11) NOT NULL DEFAULT '-1',
  `c_score` int(11) NOT NULL DEFAULT '0',
  `c_name` varchar(25) NOT NULL,
  `gender` tinyint(4) NOT NULL COMMENT '0 male, 1 female',
  PRIMARY KEY (`c_id`),
  KEY `idx_name_score` (`c_name`,`c_score`),
  KEY `idx_score` (`c_score`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

INSERT INTO `t_info`
VALUES
(1,1,60,'no1',0),
(2,2,60,'no2',0),
(3,3,60,'no3',0),
(6,4,78,'gap',1),
(7,5,95,'no5',0),
(10,8,87,'jianhaiqing',0),
(11,36,20,'no20',0),
(14,13,100,'no13',0),
(20,21,120,'no21',0),
(21,24,83,'no87-2',0);

---TRANSACTION 11465908, ACTIVE 3 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 51589, OS thread handle 139964999091968, query id 157268 172.18.70.57 root update
insert into t_info(c_id,c_class_no,c_score,c_name,gender) values (4,99,50,'gap',1)
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5547 page no 3 n bits 80 index PRIMARY of table `lock_spy`.`t_info` trx id 11465908 lock_mode X locks gap before rec insert intention waiting
------------------

Transaction 1: would lock the whole table because of  MySQL optimization itself since the table rows is very small.

One thing: if the table rows is large enough, eg. more than 1000, or others. MySQL would use the idx_score index to scan c_score>85;

Another:
Using  force index(idx_score) to query the data. Then, MySQL only lock secondary index idx_score ( also gaps), and corresponding records( not record gap).


What is the performance difference between local text index and domain text index in oracle? What is the performance difference between local text index and domain text index in oracle? Also what is the cost of rebuilding the domain text index when I have to split just one partition? Does the whole text index being rebuilt?

Thanks,

Sean Nguyen

Sean, I believe that the local text index is part of the Oracle Text feature of Oracle that is specific to working with documents, such as Web sites, digital libraries, document or warehouses. While the Domain Indexes are indexes that we use in day-to-day.

The following documentation will be helpful:
About Oracle Text:
http://docs.oracle.com/cd/B28359_01/text.111/b28303/overview.htm
(In the third chapter there is information about the indexes)

About the Domain Indexes:
http://docs.oracle.com/cd/B10500_01/appdev.920/a96595/dci07idx.htm


More than 4000 chars gives string literal too long error on oracle While updating a field that will have more than 4000 chars, I was given a error :

ORA-01704: string literal too long

So, going through few blogs, I got this:

declare
    vClobVal varchar2(32767) := 'long text'
begin
    update FMS_K_OFFICEWISE_LETTER set FKOL_LETTER_BODY = vClobVal
    where FKOL_OFFICEWISE_LETTER_ID=240;
end;

This worked for me when fired at Toad.
Now, I created a stored procedure and compiled as :

CREATE OR REPLACE PROCEDURE FMIS3.UPDATE_LETTER_BODY  (
    body_text IN FMS_K_OFFICEWISE_LETTER.FKOL_LETTER_BODY%type,
    condition_id IN FMS_K_OFFICEWISE_LETTER.FKOL_OFFICEWISE_LETTER_ID%type
) IS
begin
    update FMS_K_OFFICEWISE_LETTER set FKOL_LETTER_BODY = body_text
end;

which is called as :

 call UPDATE_LETTER_BODY('long string',201);

and this does not work for more than 4000 chars again. Can't I define the size of varchar2 as it gave me error? Any suggestions ?

Alex Poole is correct, the call is in an SQL context which can only handle 4000 characters.  You could solve this by calling the method in a block like this:

Begin
   UPDATE_LETTER_BODY('long string',201);
End;
/

Other options include using another method to call Update_Letter_Body, creating a method to be called repeatedly to add text over 4000 characters, or using multiple input parameters.


Partition of a table - how to minimize the time offline? How to check if all the statistics were updated? As you ca see on the picture, I have a table called "tblsapdispatch" that has 55,707,259 records.

Last sunday 30-aug-2015 they run a big update on this table, and I can see that the statistics were updated.

I am considering partitioning this table because I parts of it are never updated, just need to be there for reading purposes.

The primary key is an identity field, as you can see on the picture below.

questions:

1) this is a very busy table, what can I do to minimize the impact of having to lock the table and rebuild the clustered index?

2) if I leave the table as it is, how can I be sure that all the statistics for all the index were updated? Is there a script for this?

  This is the way I am currently checking when the statistics where last
  updated.

 declare @dbname nvarchar(128)

   select @dbname = db_name()

    DECLARE @SQL NVARCHAR(MAX)
    --DECLARE @ParamDefinition NVARCHAR(MAX)
    DECLARE @TABLEID INT
    DECLARE @TABLEID_STR VARCHAR(15)

    DECLARE  @log NVARCHAR(MAX)
            ,@vCrlf CHAR(2);

SELECT @vCrlf = CHAR(13)+CHAR(10);

select @TABLEID = object_id from sys.tables where name = 'MYTABLE'

SELECT @TABLEID      = COALESCE(@TABLEID,0)
SELECT @TABLEID_STR  = CAST( @TABLEID AS VARCHAR)

SELECT @SQL = ' USE ' + @dbName + ';' + @vCrlf  +
              ' SET NOCOUNT ON '  + @vCrlf  +
              ' SET DATEFORMAT DMY '  + @vCrlf  +
              ' SET DEADLOCK_PRIORITY NORMAL; '  + @vCrlf  +
              ' SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ' + @vCrlf +

'SELECT o.object_id,
       s_name=s.name,
       o.name,
       ddps.row_count
       ,[Statistics_Updated]=STATS_DATE(I.OBJECT_ID,I.INDEX_ID)
FROM sys.indexes AS i
  INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
  INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
  INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID
  AND i.index_id = ddps.index_id
WHERE i.index_id < 2  ' + @vCrlf +

    CASE WHEN @TABLEID = 0 THEN '' + @vCrlf
                           ELSE ' AND ( i.object_id = ' + @TABLEID_STR + ') ' + @vCrlf
    END +

' ORDER BY o.NAME ' + @vCrlf

    PRINT CAST (@SQL AS NTEXT)
    --EXEC (@SQL)

from the technical article Statistics Used by the Query Optimizer in Microsoft SQL Server 2008statistics are out of date when:

  The table size has gone from 0 to >0 rows.
  The number of rows in the table when the statistics were gathered      was 500 or less, and the colmodctr of the leading column of the
  statistics object has changed by more than 500 since then.
  The table had more than 500 rows when the statistics were gathered,    and the colmodctr of the leading column of the statistics object has
  changed by more than 500 + 20% of the number of rows in the table
  when the statistics were gathered.

Moreover you can use dmv sys.dm_db_stats_properties to check when stats were last updated

SELECT
OBJECT_NAME([sp].[object_id]) AS "Table",
[sp].[stats_id] AS "Statistic ID",
[s].[name] AS "Statistic",
[sp].[last_updated] AS "Last Updated",
[sp].[rows],
[sp].[rows_sampled],
[sp].[unfiltered_rows],
[sp].[modification_counter] AS "Modifications"
FROM [sys].[stats] AS [s]
OUTER APPLY sys.dm_db_stats_properties ([s].[object_id],[s].[stats_id]) AS [sp]
WHERE [s].[object_id] = OBJECT_ID(N'table_name_here');

Also suggest you to go through article Understanding When Statistics Will Automatically Update


Script out Oracle DDL in an automated fashion Oracle SQL Developer is able to export DDL through Tools -> Database Export... This works very well, but requires manual intervention.

I know of DBMS_METADATA.get_ddl(), but have found that the export isn't perfect.  I ran into issues where the exported DBMS_METADATA DDL wasn't usable without first fixing up problems like breaks in the middle of a keyword, and worse. However, if anyone knows a way of exporting DDL through DMBS_METADATA that can run without manual fixes, that'd be a great solution too.

Basically, I am looking for an automatic/scriptable way to export DDL identical to what is exported through the manual way.

How can I do that?

The reason you are having problems with dbms_metadata.get_ddl is that it outputs CLOBs which can be up to 4GB in size.  By default, SQL*Plus and Oracle SQL Developer truncate long text so they don't trash the client with large gobs of text.

It's very easy to override this behavior in SQL*Plus with a few SET commands and get clean DDL.

The script you need is:

-- Run this script in SQL*Plus.

-- don't print headers or other crap
set heading off;
set echo off;
set pagesize 0;

-- don't truncate the line output
-- trim the extra space from linesize when spooling
set long 99999;
set linesize 32767;
set trimspool on;

-- don't truncate this specific column's output
col object_ddl format A32000;

spool sys_ddl.sql;

SELECT dbms_metadata.get_ddl(object_type, object_name, owner) || ';' AS object_ddl
FROM DBA_OBJECTS
WHERE
      OWNER = 'SYS'
  AND OBJECT_TYPE IN (
      'TABLE'
    , 'INDEX'
    , 'SEQUENCE'
    , 'VIEW'
  )
ORDER BY
    OWNER
  , OBJECT_TYPE
  , OBJECT_NAME
;

spool off;