login failed for user 'domain\localMachineName$' login failed for user 'domain\localMachineName$'. Reason: Could not find a login matching the name provided. [CLIENT: ]

This appeared on my new SQL Server right after I added it to an existing Windows cluster. It occurs exactly every 5 minutes. Seems a local resource is trying to log in but I can't see what it could possibly be related to.

This is how the NETWORK SERVICE account from localMachineName presents itself to SQL Server (and file shares, and web sites, etc).

You need to profile "Login Failed" events (or XE Capture or whatever) to identify the program and/or Windows PID


MongoDB Auditing vs. Profling I wanted in the first place to log connections to a collection and while I was searching I found that it's possible to configure profiling and auditing and logging.

So my question is : What's the difference between the 3 and when to use which?

Profiling collects information about "what, when and how information was queried + how much time it took + what was DB engines solution"

Auditing tell "who did what and when" (who red/accessed f.ex. user information)

Logging is higher level information telling what DB did and when. When client makes/closes connection, new index is created, data is replicated, engine errors happened...

So, if law says that "we need to know who (as a person) accessed specific information", you do auditing.

To find out "which queries are slowest/take time and WHY" you use profiling.

To know what's happening and what are errors on database level, you use log files.


Creating Database Tables for Reviews functionality that incorporates Tags/Categories I'm wanting to create some simple website functionality through a little database idea I have in order to give myself the ability to create/retrieve customer reviews based upon tags/categories. More specifically, the reviews are not actually being input by the reviewer, they are being aggregated from outside sources and manually placed in the database by myself so that I can retrieve them at later dates and in specific site sections based upon the associated tags/categories that I place on them.

Listed below are the tables and their data structures that I've come up with:

The Rating Details Table would have at least the following fields:

**********************************************************************************
| rating_details_id   | BIGINT(20)   | UNSIGNED | NOT NULL | AUTO_INCREMENT | PK |
| professional_rating | INT          | UNSIGNED | NOT NULL |                |    |
| efficiency_rating   | INT          | UNSIGNED | NOT NULL |                |    |
| referral_rating     | INT          | UNSIGNED | NOT NULL |                |    |
| rating_comment      | TEXT         |          |          |                |    |
| rating_author       | VARCHAR(200) |          | NOT NULL |                |    |
| rating_date         | DATE         |          | NOT NULL |                |    |
**********************************************************************************

The Rating Tag Table – I'm assuming – would have the following fields:

**********************************************************************************
| rating_category_id   | BIGINT(20)  | UNSIGNED | NOT NULL | AUTO_INCREMENT | PK |
| rating_category_name | VARCHAR(75) |          |          |                |    |
**********************************************************************************

The final Main Rating Table – I'm assuming – would then consist of:

**********************************************************************************
| rating_id           | BIGINT(20)   | UNSIGNED | NOT NULL | AUTO_INCREMENT | PK |
| rating_details_id   | BIGINT(20)   | UNSIGNED | NOT NULL | AUTO_INCREMENT | FK |
| rating_category_id  | BIGINT(20)   | UNSIGNED | NOT NULL | AUTO_INCREMENT | FK |
**********************************************************************************

Question 1): Do these tables look correct for what I'm trying to accomplish? If not, please provide me with some feedback as to how I should structure these tables and their data.

Question 2): If these tables are correct could you provide me with some assistance as to how I might query these tables through joins so that I could add data and retrieve date?

Example 1) How would I query the DB to insert a new review where the categories are foo and foo2?
Example 2) How would I query the DB to retrieve all reviews where the category is foo?

I hope this all makes sense and I really appreciate any and all useful insight that you might be able to offer.

The tables can be queried in a few different ways depending on what you want to do. If you wish to (Example 2) extract all the Reviews with a particular Category you can create a procedure like this:

SELECT review_id, professional_rating, efficiency_rating, referral_rating...
FROM Review r
INNER JOIN Review_Category rc ON r.review_id = rc.review_id
INNER JOIN Category c ON rc.category_id = c.category_id
WHERE c.review_category_name = 'foo'

For Example 1, if you want to insert a new review with 2 categories, you generally do this in 2 steps, handled by the application calling the database procedures. First insert the review with something like:

CREATE PROCEDURE Review_Insert (<review table values>, OUT NewReviewID bigint)
BEGIN
    INSERT INTO Review (<column list>)
    VALUES (<value list>)

    SELECT NewReviewID = LAST_INSERT_ID()
END

You should then be storing the output of the NewReviewID in your application, which you then pass into the next procedure for associating the Review with the Category, something like:

CREATE PROCEDURE Review_Category_Insert (ReviewID BIGINT, CategoryID BIGINT)
BEGIN
    INSERT INTO Review_Category (review_id, category_id)
    VALUES (ReviewID, CategoryID)
END

This last one needs to be called for each Category that is associated with a Review.

You can do only one trip to the database too, if that is what you need, however it appears you could have 1 category associated with a review or 100 in one trip, which makes defining the parameters in your procedure difficult (not impossible, just difficult).


Ensure total order in table via constraint Suppose I have a table

create table log (id serial, logged_at timestamp, event_type integer)

Can I efficiently (without making every row writable only once or by some special user) ensure that
∄ (r1, r2) ∈ log X log (r1.id > r2.id ∧ r2.logged_at > r1.logged_at)

So events in log partially ordered by time and we can always have total order via monotonically increasing sequence.

This sounds like an occurrence of: can the database enforce constraints across rows?
(I assume that the X sign in the formula means "such that")

As a general rule, the answer is no, because when a row is being modified by a transaction, the state of the other rows is always uncertain due to possible modifications by not-yet-committed concurrent transactions.

An application may handle this correctly by implementing an adequate locking strategy preventing concurrent writes, or checking the constraint at the serializable isolation level, but that does not prevent a random session to just issue an UPDATE in the table without these safety measures, that would potentially result in quietly violating the constraint.

There are few kinds of table-level constraints that the SQL engine knows how to enforce, like unique constraints and exclusion constraints, but your formula doesn't look like an expression that could be used by such constraints.


Is there a way to get disc space remaining using an SQL query on an Oracle database? We experienced a problem recently where the disc space ran low and some queries started giving unpredictable results. The root cause was that we'd take too many restore points and filled up the disc.

I'd like to be able to monitor this. My context is a big corporate, highly separated functional roles, so I only have access to the SQL query on the box.

My question is: Is there a way to get disc space remaining using an SQL query on an Oracle database?

You should look here at Managed Tablespace Alerts, notably this bit where you can monitor by two criteria with warning and critical thresholds.

    By percent full

For both warning and critical thresholds, when space used becomes greater than or equal to a percent of total space, an alert is issued.

    By free space remaining (in kilobytes (KB))

For both warning and critical thresholds, when remaining space falls below an amount in KB, an alert is issued. Free-space-remaining thresholds are more useful for very large tablespaces.


Multi-threaded inserts vs serial I have a large database application on SQL Server 2012 Standard Edition which receives data from hardware in chunks of 500-1000 (100 byte) rows every few seconds through a stored procedure. Would a multi-threaded application with each thread using a database connection, inserting the block of rows work faster than a single thread connection serially calling the stored procedure multiple times? What is the best way to insert the data for the highest performance? Are there clustered index designs which would work better in this scenario?

Thanks, Tony

Consider using a table-valued parameter to pass many rows of data as a single proc call, or alternatively, bulk copy directly into the table.  These techniques will improve insert throughput by orders of magnitude compared to singleton inserts, even if those are multi-threaded.

An incremental clustered index will provide the best insert performance against spinning media.


Generate balance from Chart of Accounts I have the following tables in postgres

 ChartOfAccounts
| Id          | int     |
| AccountName | varchar |
| ParentId    | int     | FK self references Id
| Level       | int     | Check constraint Level >= 1 && Level <= 4

 Transactions
| Id          | int     |
| Amount      | int     |
| AccountId   | int     | FK references Accounts(Id)

The Chart of Accounts is a tree structure four levels deep, and a transaction will only ever reference the fourth level.

I need to be able to find the balance (sum of all trasaction amounts) of any or multiple accounts in the Chart of Accounts, no matter what level.

I've been able to come up with specific queries that will find the balance of a 4th level account and another for the 3th level, but not one that I can use for any level.

I also wonder if this query could be too slow when you have hundreds of thousands or even millions of rows in the Transactions table, but I'm guessing as long as its well indexed it will be ok.

edit:

The user can only add level 4 accounts, levels 1 to 3 are locked in and can't be changed.

I want to be able to query these balances by account_id, heres the query I currently have for level 3 account balance.

SELECT a.parent_id, SUM(t.amount) balance
FROM transaction t
JOIN account a ON t.account_id = a.id
WHERE a.parent_id IN (1, 4, 7) -- IDs of the accounts which I want the balance
GROUP BY a.parent_id;

The expected output is

| account_id | balance |
| 1          | 1500    |
| 4          | -2500   |
| 7          | 550     |

You need a hierarchical or recursive query, like

with recursive
 startwith(val) as (
  select 2
  union select 5
 ), AccountTree AS (
   SELECT
      id,
      ParentId,
      id as tree_start,
      name
    FROM ChartOfAccounts
    WHERE id in (select val from startwith)
   UNION
   SELECT
      ca.id,
      ca.ParentId,
      s.tree_start,
      ca.name
     FROM ChartOfAccounts ca
     INNER JOIN AccountTree s ON s.id = ca.ParentId
)
SELECT tree_start, sum(t.amount)
  FROM AccountTree at
  left outer join Transactions t on t.AccountId = at.id
  group by tree_start

See this DB Fiddle


Live monitoring SQL statements on SQL Server How can I monitor running SQL Statements for my SQL Server, is it possible using only SQL Server without 3rd party tools?

As an alternative can anybody recommend any free tool which can monitor running statements over ODBC connections?

There's Sql Server Profiler which comes with the installation. It allows you to monitor all SQL statements with various filter criteria. You could also pair it up with PerfMon to investigate performance.

MSDN Sql Server Profiler

I also found this site Receiving Profiler Events. Which allows real-time tracking in .NET.


Row Locking in Postgres I have table named 'car'

              Table "db145004db.car"
  Column  |  Type  | Collation | Nullable | Default
----------+--------+-----------+----------+---------
 car_no   | bigint |           | not null |
 car_name | text   |           |          |
Indexes:
    "car_pkey" PRIMARY KEY, btree (car_no)

Table Content:

 car_no |  car_name
--------+------------
      1 | first_car
      2 | second_car
      3 | third_car

I ran this following two transaction,

Transaction 1:

begin ;
select * from car where car_no=2 for update;

Transaction 2:

begin ;
select * from car where car_no=2 for update; (waiting for first transaction to release lock)

My 'pg_locks' table looks like:

  locktype    | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid  |        mode         | granted | fastpath
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+---------------------+---------+----------
 relation      |   103777 |   143022 |      |       |            |               |         |       |          | 4/75               | 11043 | AccessShareLock     | t       | t
 relation      |   103777 |   143016 |      |       |            |               |         |       |          | 4/75               | 11043 | RowShareLock        | t       | t
 virtualxid    |          |          |      |       | 4/75       |               |         |       |          | 4/75               | 11043 | ExclusiveLock       | t       | t
 relation      |   103777 |   143022 |      |       |            |               |         |       |          | 5/144              | 11150 | AccessShareLock     | t       | t
 relation      |   103777 |   143016 |      |       |            |               |         |       |          | 5/144              | 11150 | RowShareLock        | t       | t
 virtualxid    |          |          |      |       | 5/144      |               |         |       |          | 5/144              | 11150 | ExclusiveLock       | t       | t
 relation      |   103777 |    11577 |      |       |            |               |         |       |          | 3/141              | 11263 | AccessShareLock     | t       | t
 virtualxid    |          |          |      |       | 3/141      |               |         |       |          | 3/141              | 11263 | ExclusiveLock       | t       | t
 transactionid |          |          |      |       |            |         10204 |         |       |          | 5/144              | 11150 | ShareLock           | f       | f
 tuple         |   103777 |   143016 |    0 |     2 |            |               |         |       |          | 5/144              | 11150 | AccessExclusiveLock | t       | f
 transactionid |          |          |      |       |            |         10204 |         |       |          | 4/75               | 11043 | ExclusiveLock       | t       | f

I can't see any locks related to rows/tuples (car_no=2). Also, locktype and mode are little confusing(I can't understand what they tend to convey). As a newbie to psql, I am totally confused. Can anyone help interpreting 'pg_locks', with respect to row-level locking, with respect to above scenario.

I came across the postgres docs regarding locking , which states that

  Although tuples are a lockable type of object, information about row-level locks is stored on disk, not in memory, and therefore row-level locks normally do not appear in this view. If a transaction is waiting for a row-level lock, it will usually appear in the view as waiting for the permanent transaction ID of the current holder of that row lock.

I got my answer.


Does mysql "lock" (no other process can take from it) the memory allocated to it? I have a 64GB memory machine, and I set mysql buffer pool to be 40GB. So MySQL is using up to 40G memory while it's running.

I kicked off a java application that requires more than 20GB memory, so the java application starts to consume more and more memory and eventually the total 64G memory is not enough for both java and mysql.

Now, the interesting problem shows: mysql crashed (see graph here). But to my understand, shouldn't mysql stays the same, and instead Java crashes? Since the 40G memory is already allocated to mysql, the java application shouldn't take it away right?

Thank you

If you check kernel log, you should see OoM Killed messages.. Kernel will usually kill the largest offender which is MySQL in ourcase.


SQL Server Ubuntu server - failure: remote connection from Win 7 Our analytical lab (chemistry) has an information management system (LIMS) that uses a Microsoft SQL Server database. Technicians interact with the system through a Windows form application that is closed-source and permits only SQLOLEDB.1, MSOLEDBSQL, and SQLNCLI11.1 (Native Client 11) connections (choices are picked from a dropdown box at login). Our production server is SQL Server 2016 running on Windows Server 2012.
The lab has several analytical instruments that are constrained to Win 7 and Win XP for data acquisition. This is an immutable constraint.
On the production setup, all machines make a connection with legacy SQLOLEDB.1 but Win 10 Machines can alternatively connect with MSOLEDBSQL, and SQLNCLI11.1 as well. Win 7 and XP machines cannot.
I am prototyping a SQL Server 2019 upgrade on a Ubuntu server. As configured, no machines can connect to it using the legacy SQLOLEDB.1 connection. The LIMS application is able to connect to it from Win 10 using either MSOLEDBSQL or SQLNCLI11.1. The Win 7 and XP machines cannot connect with any of the protocols.
If this is to work, I have two choices:

Somehow configure MSSQL on the Ubuntu server to accept SQLOLEDB.1 connections.
Somehow configure XP and Win 7 machines to make MSOLEDBSQL or SQLNCLI11.1 connections.

I cannot figure out how to do either one.
Error messages: "could not connect to the database" (when using sqloledb1 indicating that the server does not accept this protocol as configured). "item cannot be found in the collection corresponding to the requested name or ordinal" (when using Native Client 11 indicating that Win 7 does not have the necessary connection capabilities).
As asked, I need to either configure the server to accept SQLOLEDB1 or configure Win 7 to make SQLNCLi11.1 connections.
The Win 7 machine never attempts to make the Native Client 11 connection because it doesn't have the required libraries for this protocol. It does attempt to make the sqloledb1 connection but it cannot because the SQL Server 2019 implementation is not, at least yet, configured to negotiate this legacy oledb connection. There is no error in the server log. The connect simply fails.
I should add that the Win 7 machine is able to make a connection to the production SQL Server 2016 database. But... none of our Win 7 or XP machines has ever been able to make a connection with anything but legacy OLEDB.
The MSOLEDBSQL driver will not install out of the box. The error thrown is KB2999226 Check Failed. I downloaded KB2999226-x86.msu but manually installing it is beyond me because the only mechanism I can see for applying updates on XP is to do it through the web interface (non-existent site). I can extract the .msu but the sprawling directory tree is perplexing. Perhaps there's a way to configure the server to accept legacy SQLOLEDB.1.

SQL Server Native Client 10 works for Win 10, Win 7, and Win XP.
I'm not sure not sure it was necessary yet but, for Win XP, I also installed windowsxp-kb4019276-x86-embedded-enu (and hacked the registry to enable TLS 1 & 2).
$2M worth of Mass Spectrometers and a $100K ICP-AES can now use the LIMS with SQL Server 2019.


Query data that does not fulfill constraints in table in Oracle DB In Oracle I have a lot of tables in a given schema. Some of the table constraints in the schema have been disabled.

I have to know for all tables in a given schema with any of the constraints deactivated, what are the (data) rows that would not fulfil the deactivated constraints.

Question

Is there a way to easily collect all the rows that would produce an error if I were to decide to enable the constraints again?

So far, I would try to go constraint by constraint, create a query for each table and run the query. But I wonder whether there is anything that may do the work quickly and less error prone. If possible, the solution should use the information available in view USER_CONSTRAINTS or any similar one.

I have checked the constraint documentation in Oracle, in which the constraint clause may use ENABLE NOVALIDATE, which allows for having non-compliant rows of data for the existing values until they are modified.

However, I am looking for a solution that would allow me to fix the data before I re-enable the constraints.

Reporting Constraint Exceptions

  You must create an appropriate exceptions report table to accept
  information from the EXCEPTIONS option of the ENABLE clause before
  enabling the constraint. You can create an exception table by
  executing the UTLEXCPT.SQL script or the UTLEXPT1.SQL script.

  The following statement attempts to validate the PRIMARY KEY of the
  dept table, and if exceptions exist, information is inserted into a
  table named EXCEPTIONS:

ALTER TABLE dept ENABLE PRIMARY KEY EXCEPTIONS INTO EXCEPTIONS;

  If duplicate primary key values exist in the dept table and the name
  of the PRIMARY KEY constraint on dept is sys_c00610, then the
  following query will display those exceptions:

SELECT * FROM EXCEPTIONS;

  The following exceptions are shown:

fROWID               OWNER      TABLE_NAME      CONSTRAINT
------------------  ---------  --------------  -----------
AAAAZ9AABAAABvqAAB  SCOTT      DEPT            SYS_C00610
AAAAZ9AABAAABvqAAG  SCOTT      DEPT            SYS_C00610


Postgres hide active querys I need to encrypt some columns in the postgresql for privacy reasons. I would like to use the pgcrypt and encrypt the columns with an asymmetric key pair and the passphrase.

Now you can display currently executed queries. There you will see the private key and the passphrase. Is there a way to protect the executed / active queries from view?

Use parameters, then only the $1 etc. markers are visible in pg_stat_activity.


Postgresql - Large object exists in pg_largeobject table but cannot open with lo_open? I have a problem with opening Large Object in postgresql version 9.2,

select * from pg_largeobject where loid = '19423';

will return data like this:

loid oid;page no integer;data bytea
19423;0;"<Comment>ripple height</Comment><Units>meter</Units><Model>TRIM2</Model><Title>Results of TRIM model (Hartmut Kapitza): bottom temperature, bottom salinity, bed shear stress generated by currents. Results of WAM model (Heinz Guenther, Ralf Weisse): bed she (...)"

However, when I try to open with

select lo_open(19423, x'40000'::int);

It returns error:

ERROR: large object 19423 does not exist
SQL state: 42704

The result of this query:

select * from pg_largeobject_metadata where oid = 19423

is empty: lomowner oid; lomacl aclitem[]

What is this kind of error in Postgresql? I have no clue about it. The data was restored from 1 postgresql dump .sql file.

Large objects were restored from a dump with this command:

COPY pg_largeobject (loid, pageno, data) FROM stdin;

The problem is that it's not sufficient, because since PostgreSQL 9.0, which added access privileges to large objects, they are stored into two tables:
pg_largeobject_metadata with one row per object and a unique index on oid, and pg_largeobject, which one row per page of data per object.

So the above way of dumping large objects is obsolete, now it looks like, for each large object:

BEGIN;
SELECT pg_catalog.lo_open('16401', 131072);
SELECT pg_catalog.lowrite(0, '\x23207e2f2e6261736872633a2....')
SELECT pg_catalog.lo_close(0);
COMMIT;

How to fix the problem of the missing entries in pg_largeobject_metadata?

Because oid is a system column, I don't think you can just insert into it to manually to create the missing entries.
A clean way to deal with the problem would be to generate a script looking like the above based on the current contents of pg_largeobject, then truncate pg_largeobject, then play the script to reimport the data the right way.


Is column order reliable in UPDATE? Can we reply on the order of columns in an UPDATE?

SET @x = 0
UPDATE table1 SET
col1=IF(col1 IS NULL, NULL, @x:=@x+col1),
col2=IF(col2 IS NULL, NULL, @x:=@x+col2),
col3=IF(col3 IS NULL, NULL, @x:=0)

Is it guaranteed that col1 is UPDATEd before col2?

I do care about the order in calculating the value @x.

The order is unreliable and is subject to change on different query evaluation plans.

However, I found a reliable hack around this. Please consider my presentation Programmatic Queries: Things you can Code with MySQL. In particular see slides 10 - 15.

The trick presented utilizes the known order of evaluation in a CASE clause, as follows:

SELECT
  CASE
    WHEN (@c3 := @c1 + @c2) IS NULL THEN NULL
    WHEN (@c1 := @c2) IS NULL THEN NULL
    WHEN (@c2 := @c3) IS NULL THEN NULL
    ELSE @c3
  END AS seq
FROM
  mysql.help_topic,
  (SELECT @c1 := 1, @c2 := 0) s_init
LIMIT 10;

The CASE expression has to evaluate the WHEN clauses by order of appearance (to be more specific, it does so, and it doesn't make sense to do otherwise).

In the above none of the WHEN expressions make for a true value, which is why the CASE clause keeps evaluating them one by one. This gives you your required order.


how to update new password in maintenance plan I have changed the SQL account password but my all backups have failed. How do I update the new password manager connection in the maintenance plan from the backup?

Navigate to SSMS --> Management-->Maintenance plans

Click on you Maint Tasks -->Right Click and select Modify

On this page you find Manage Connections

Check the Manage connections there you find options to change the credentials.


View SQL Server Agent jobs -> MSDB roles When attempting to view the jobs' properties in SSMS, the users would get the error execute was denied on the object sp_help_targetserver. This is a sys SP in MSDB.

The users have

SQLAgentReaderRole
SQLAgentUserRole

which should be sufficient to view the jobs themselves. These roles are assigned to their AD group. All the users are Win domain users that belong to the AD groups that have those permissions.

Does anyone know why management studio tries to execute sp_help_targetserver just to view jobs? Executing sp_help_job works.

SQL Server Agent Jobs can be targeted at non-local servers, via the "Target Server" property.

In order to make the "Target multiple servers" option enable, you need to setup a Master Server which enables automated administration across an enterprise.

SSMS is running the msdb.dbo.sp_help_targetserver stored procedure to obtain details about the servers that may potentially be listed in the "Targets" property window.

In order to run the sp_help_targetserver stored procedure, the user must be a member of the sysadmin role.  If the user is not a member of sysadmin, they can simply ignore the error message.  They should, however, be aware that in a Master Server environment the properties window may not show accurate details.

Running msdb.dbo.sp_help_job will, by default, run sp_help_jobserver.  However, if you run EXEC dbo.sp_help_job @job_name = 'some_job', @job_aspect = 'JOB';, the target servers are not checked, and not displayed.  This may provide a good way for non-sysadmin users to look at job properties, although not via the GUI.

FYI, the Microsoft Docs for SQL Server Agent Roles says this about SQLAgentReaderRole:

  SQLAgentReaderRole includes all the SQLAgentUserRole permissions as well as permissions to view the list of available multiserver jobs, their properties, and their history. Members of this role can also view the list of all available jobs and job schedules and their properties, not just those jobs and job schedules that they own. SQLAgentReaderRole members cannot change job ownership to gain access to jobs that they do not already own. Only the Jobs node in SQL Server Management Studio Object Explorer is visible to members of the SQLAgentReaderRole.

The above seems at odds with the Docs for sp_help_targetserver, which clearly states the user must be a member of the sysadmin role.


Forcing postgres to use a GIN index on a varchar[] In a postgres 9.4 database, I have a table with a field identifiers: varchar(512)[] that contains a list of identifiers for each row. When adding a new row, I want to make sure its identifiers list does not overlap with any existing row, so I want to search first for such a conflicting row using a GIN index on that column.

    Column      |           Type           |                        Modifiers
----------------+--------------------------+-----------------------------------------------------------
id              | integer                  | not null default nextval('my_table_id_seq'::regclass)
identifiers     | character varying(512)[] |
... other columns ...

Indexes:
    "my_table_pkey" PRIMARY KEY, btree (id)
    "gin_idx_identifiers" gin (identifiers) WITH (fastupdate='on')
    ... other indexes ...

The problem is that by default, the following query does not use the GIN index:

EXPLAIN ANALYZE
SELECT * FROM "my_table"
WHERE "my_table"."identifiers" && ARRAY['a_sample_identifier']::varchar(512)[] LIMIT 1;

                                                            QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..20.97 rows=1 width=1368) (actual time=31120.985..31120.986 rows=1 loops=1)
   ->  Seq Scan on my_table  (cost=0.00..4318413.15 rows=205920 width=1368) (actual time=31120.983..31120.983 rows=1 loops=1)
         Filter: (identifiers && '{a_sample_identifier}'::character varying(512)[])
         Rows Removed by Filter: 14572058
 Planning time: 0.135 ms
 Execution time: 31121.023 ms

If I set enable_seqscan = off, then the index is used (and the query is much faster, unsurprisingly):

                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1755.88..1757.62 rows=1 width=1368) (actual time=0.065..0.065 rows=1 loops=1)
   ->  Bitmap Heap Scan on my_table  (cost=1755.88..359284.85 rows=205920 width=1368) (actual time=0.064..0.064 rows=1 loops=1)
         Recheck Cond: (identifiers && '{a_sample_identifier}'::character varying(512)[])
         Heap Blocks: exact=1
         ->  Bitmap Index Scan on gin_idx_identifiers  (cost=0.00..1704.40 rows=205920 width=0) (actual time=0.056..0.056 rows=1 loops=1)
               Index Cond: (identifiers && '{a_sample_identifier}'::character varying(512)[])
 Planning time: 0.126 ms
 Execution time: 0.102 ms
(8 rows)

However, I have read that setting enable_seqscan = off globally is dangerous. How should I force postgres to use the index?

You can use an EXCLUDE constraint to have the system automatically do the check for you.  Once in place, it will automatically throw an error if you try to insert a row that violates the constraint.  This is probably your best option.  If you first check manually and then insert the row, there could be a race condition which allows two conflicting rows to be inserted successfully.

If for some reason you really need to do the check manually, you could re-write the query to use a COUNT(*) rather than LIMIT 1, and then check to see if the count is zero or not.

The problem is that the planner things the seq scan will find one matching row very quickly, and can then stop.  Writing the query with a COUNT(*) will force the planner to realize the true cost of the seq scan.  Alternatively, you could just drop the LIMIT 1 from the query, and on the client side have it fetch only one row and then not fetch any more.


Why ORA-00932: Inconsistent Types error? Sorry for the meaningless title, but I couldn't think up a better one. I'm using Oracle 11g r2.

The following query raise ORA-00932 error:

SELECT BBB.FIELD1 FROM TABLE0 AAA
  JOIN (SELECT * FROM (SELECT AAA.*, RANK() OVER (PARTITION BY SUBSTR(FIELD1,1,1) ORDER BY SUBSTR(FIELD1,1,1),ROWNUM) AS RANK
      FROM TABLE1@DBLINK AAA WHERE UPPER(NAME)!='XXX')
    WHERE RANK<=1) BBB ON CAST(CAST(CAST(SUBSTR(BBB.FIELD1,2,LENGTH(BBB.FIELD1)-2) AS NUMBER) AS VARCHAR2(10))||SUBSTR(BBB.FIELD1,LENGTH(BBB.FIELD1)) AS VARCHAR2(4000))=CAST(CAST(CAST(SUBSTR(AAA.FIELD0,2,LENGTH(AAA.FIELD0)-2) AS NUMBER) AS VARCHAR2(10))||SUBSTR(AAA.FIELD0,LENGTH(AAA.FIELD0)) AS VARCHAR2(4000));

However, the following query works perfectly fine.

SELECT * FROM TABLE0 AAA
  JOIN (SELECT * FROM (SELECT AAA.*, RANK() OVER (PARTITION BY SUBSTR(FIELD1,1,1) ORDER BY SUBSTR(FIELD1,1,1),ROWNUM) AS RANK
      FROM TABLE1@DBLINK AAA WHERE UPPER(NAME)!='XXX')
    WHERE RANK<=1) BBB ON CAST(CAST(CAST(SUBSTR(BBB.FIELD1,2,LENGTH(BBB.FIELD1)-2) AS NUMBER) AS VARCHAR2(10))||SUBSTR(BBB.FIELD1,LENGTH(BBB.FIELD1)) AS VARCHAR2(4000))=CAST(CAST(CAST(SUBSTR(AAA.FIELD0,2,LENGTH(AAA.FIELD0)-2) AS NUMBER) AS VARCHAR2(10))||SUBSTR(AAA.FIELD0,LENGTH(AAA.FIELD0)) AS VARCHAR2(4000));

The only difference is that I selected all (*) columns instead of specifying one specific column. Why is this happening?

EDIT

The error raised is (exactly as it is):

ORA-00932: inconsistent datatypes: expected  got
ORA-02063: preceding line from DBLINK
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:

When you are casting a varchar to a number you have to make sure this is possible for ALL values in this table. So in your case also for the values you think are filtered by "WHERE UPPER(NAME)!='XXX'".
The reason is the optimizer does not have to follow a specific order for the logic in your query. So it could do the casting and joining the tables frist and only after do the filtering specified in the inner query.
Changing the * to a specific field can change the execution plan the optimizer chooses, hence you could have the problem with one version and not the other.

You could use your own function to avoid this error:

create or replace function tonumberorzero(txt in varchar2) return number
 is
   retval number;
 begin
    return to_number(txt);
 exception
    when invalid_number then return 0;
 end;


choose MAX sub-category if null Here is some made-up data as an example:

Category
Sub-Category
COUNT

Furniture
Chairs
25

Furniture
Null
15

Technology
Null
10

Technology
Phones
35

What I'm trying to do is... Keeping CATEGORY fixed, choose the non-null subcategory.
so this would be what would happen:

Category
Sub-Category
COUNT

Furniture
Chairs
25

Furniture
Chairs
15

Technology
Phones
10

Technology
Phones
35

and this would be the output:

Category
Sub-Category
COUNT

Furniture
Chairs
40

Technology
Phones
45

I figured out how to do this within Tableau, but I need to figure out how to do this in SQL.
Here is the solution inside Tableau:
IFNULL ([Sub-Category],
{FIXED [Category] : MAX([Sub-Category]) } )

Btw:  there is much more to why I'm doing this, and right now it looks like "why would she want to do this?", but there is a reason that makes sense, but can't explain because I'm not allowed to show data from my job because it is confidential. The output would be a result of combining the like terms together.
Thank you!

With the data that I'm actually using (vs. the data in the example), I had to add in an additional field for the partition (not shown here), but it would look something like this...
CASE
  WHEN SUBCATEGORY IS NULL THEN
    FIRST_VALUE(SUBCATEGORY)
    OVER (PARTITION BY ADDITIONAL_FIELD,CATEGORY
          ORDER BY SUBCATEGORY NULLS LAST)
  ELSE
    SUBCATEGORY
END AS NEW_SUBCATEGORY

Full query:
SELECT
  ORDERID,
  CATEGORY,
  NEW_SUBCATEGORY,
  SUM(COUNTS) AS NEW_COUNTS
FROM
  (
    SELECT
      ORDERID,
      CATEGORY,
      SUBCATEGORY,
      CASE WHEN SUBCATEGORY IS NULL THEN FIRST_VALUE(SUBCATEGORY) OVER (PARTITION BY CATEGORY ORDER BY SUBCATEGORY NULLS LAST) ELSE SUBCATEGORY END AS NEW_SUBCATEGORY,
      COUNT(*) AS COUNTS
    FROM ORDER_TABLE
    GROUP BY
      ORDERID,
      CATEGORY,
      SUBCATEGORY
  )T
GROUP BY
  ORDERID,
  CATEGORY,
  NEW_SUBCATEGORY;


how to query nested json objects in mongodb? {
    "_id" : ObjectId("58c7da2adaa8d031ea699fff"),
    "title" : "sdsddfasssdsdfsdffdf",
    "viewData" : [
        {
            "_id" : ObjectId("58c7f9d03ef6c93ce964d19a"),
            "widgetData" : {
                "pageIndex" : 5,
                "pageLength" : 5,
                "fetch" : 239,
                "granularity" : "day",
                "threshold" : [
                    {
                        "temperature" : {
                            "between" : {
                                "critical" : "40",
                                "warning" : "10"
                            },
                            "notification" : "Email"
                        }
                    },
                    {
                        "dewPoint" : {
                            "between" : {
                                "critical" : "40",
                                "warning" : "10"
                            },
                            "notification" : "Email"
                        }
                    },
                    {
                        "visibility" : {
                            "between" : {
                                "critical" : "40",
                                "warning" : "10"
                            },
                            "notification" : "Email"
                        }
                    }
                ],
                "measures" : [
                    "visibility",
                    "dewPoint",
                    "temperature"
                ],
                "dimensions" : [
                    "country",
                    "name",
                    "lat",
                    "lon"
                ],
                "hierarchies" : {
                    "country" : {
                        "lon" : [
                            "-3.056",
                            "-0.502",
                            "-2.1955",
                            "-2.6"
                        ],
                        "lat" : [
                            "53.497",
                            "53.031",
                            "49.44",
                            "49.2079"
                        ],
                        "name" : [
                            "CROSBY",
                            "CRANWELL",
                            "JERSEY",
                            "GUERNSEY"
                        ],
                        "country" : [
                            "ENGLAND",
                            "CHANNEL ISLANDS"
                        ]
                    }
                },
                "aggregation" : {
                    "temperature" : "MIN",
                    "dewPoint" : "MAX",
                    "visibility" : "AVG"
                },
                "widget" : {
                    "mode" : "absolute value",
                    "widgetType" : "MonitorWidget",
                    "title" : "England",
                    "description" : ""
                },
                "pinned" : true,
                "DataSource" : "MetadataDataSource",
                "mid" : "589414f51163d131285563e2"
            }
        }

how to find widget title value?

i tried

db.getCollection('mobiledashboards').find({"_id": "58c7da2adaa8d031ea699fff" },{ viewData: { $elemMatch : { "widgetData.widget.title": "England" } } })

but not working, how to solve this one.

I found the answer myself:

db.getCollection('mobiledashboards')
    .find
    (
        {
            "_id": ObjectId("58c7da2adaa8d031ea699fff")
        },
        {
            viewData: { $elemMatch : { "widgetData.widget.title" : "England" } }
        }
    )


SMS Suggesting covering all columns in an index? I'm still a bit newer to indexes and database works, so I may very well be missing something obvious.

SMS suggested adding a covered index to one of my tables that Indexes on the column I believe makes perfect sense. However, it also recommended including (covering) every other column in the table. To me this seems like it is moving the entire table into the index. Which I believe is probably a bad idea.Is SMS potentially misleading me in this case?

More generally is there any reason to cover every single column in a table?

SELECT  *
    FROM    SlideshowImages SI
    JOIN    Files F ON F.PK_FileID = SI.FK_FileID
    WHERE   F.FK_ModuleID   = 81
    AND     F.ParentType    = 2
    AND     F.FK_ItemID     = @SlideshowID
    ORDER BY F.[Order] ASC

Suggested Index by SMS - All 10 columns of the SlideshowImages table:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[SlideshowImages] ([FK_FileID])
INCLUDE ([PK_SlideshowImageID],[Title],[Caption],[IsLink],[LinkAddress],[StartDate],[EndDate],[AltText],[OpenInNewWindow])

SMS suggested adding a covered index to one of my tables that indexes on the column I believe makes perfect sense. However, it also recommended including (covering) every other column in the table.

The suggestions shown in SSMS are actually generated by the query optimizer while it searches for an efficient physical execution plan for the query. The suggestions are opportunistic and based only on a superficial analysis of the single query.

  To me this seems like it is moving the entire table into the index.

Copying rather than moving, but yes, an index that covers every column in the base table (either as a key or included column) is essentially a copy of the table's data sorted in a different way. Conceptually, this is the same as having multiple clustered indexes on a table.

  More generally is there any reason to cover every single column in a table?

It is quite unusual to do this, but there will always be very specific scenarios where it can make sense. An index that is a copy of the table data sorted in a different way will need to be maintained for every change to the base table, will consume more space on disk and in memory, and will generate more transaction log. For most applications, the costs outweigh the benefits.

  Adding the index results in times going from 70ms average to 5.8 ms including all columns and 7ms with just index and no covered columns (5 trials; Client statistics). So it seems covering all is faster.

A fully covering index means all the data needed for the query can come from that single b-tree structure. If a nonclustered index does not fully cover the query, the optimizer can choose to locate rows using the index then look up the remaining column data in the base table using the row locator stored in the nonclustered index. The extra step has a cost associated with it, which is proportional to the number of rows qualified by the index key search.

In most cases, the performance improvement offered by creating the nonclustered index on FK_FileID alone would be considered the best trade-off among the various considerations. That said, you may have very specific requirements that mean you are happy to go with the fully-covering option.


Oracle 11g encrypt traffic from clients to database server I have Oracle 11gR2 db at my work. Last week security team informs that we need to encrypt all the traffic for database users to our databases. First thing that I have checked was Oracle solution - Oracle Advanced Security but after checking the license it would cost huge money for the company. So management decided to find check other solutions.

My second idea was to create SSH tunnel from clients to DB server. But in this case i have no idea how to restrict the access from users to DB only over encrypted tunnel. Can you suggest me how to restrict the access to db server only over the tunnel? Or maybe you have other solution how to my problem.

Thank you in advance for your reply.

Check the licensing again, these options became free about 2 years ago.

http://docs.oracle.com/cd/E11882_01/license.112/e47877/options.htm#DBLIC143

  Network encryption (native network encryption and SSL/TLS) and strong
  authentication services (Kerberos, PKI, and RADIUS) are no longer
  part of Oracle Advanced Security and are available in all licensed
  editions of all supported releases of the Oracle database.


Error: Unclosed quotation mark after WHILE (@7DaysEarlierPartitionIntegerId <= @CurrentPartitionIntegerId)
    BEGIN
        Set @7DaysEarlierPartitionId
            = CAST(@7DaysEarlierPartitionIntegerId AS char)
        set @sqlCommand
            = 'Select * from '
              + quotename(@RequestUsage_Partition + @7DaysEarlierPartitionId)
              + 'where UserLogin like''r2\rohit.kharade'
        exec(@sqlCommand)
        set @7DaysEarlierPartitionIntegerId
            = @7DaysEarlierPartitionIntegerId + 1
    END

I get the error:

  Unclosed quotation mark after the character string 'r2\rohit.kh'.

How to execute the exec command with UserLogin =%r2\rohit.kharade%

You're missing the end quote, this should get you there.

WHILE (@7DaysEarlierPartitionIntegerId <= @CurrentPartitionIntegerId)
    BEGIN
        Set @7DaysEarlierPartitionId
            = CAST(@7DaysEarlierPartitionIntegerId AS char)
        set @sqlCommand
            = 'Select * from '
              + quotename(@RequestUsage_Partition + @7DaysEarlierPartitionId)
              + 'where UserLogin like ''r2\rohit.kharade'''
        exec(@sqlCommand)
        set @7DaysEarlierPartitionIntegerId
            = @7DaysEarlierPartitionIntegerId + 1
    END

I generally like to use CHAR(39) in place of multiple quotes, just to make code more readable

WHILE (@7DaysEarlierPartitionIntegerId <= @CurrentPartitionIntegerId)
    BEGIN
        Set @7DaysEarlierPartitionId
            = CAST(@7DaysEarlierPartitionIntegerId AS char)
        set @sqlCommand
            = 'Select * from '
              + quotename(@RequestUsage_Partition + @7DaysEarlierPartitionId)
              + 'where UserLogin like ' + CHAR(39) + 'r2\rohit.kharade' + CHAR(39)
        exec(@sqlCommand)
        set @7DaysEarlierPartitionIntegerId
            = @7DaysEarlierPartitionIntegerId + 1
    END

Bonus thought, just as another, more readable version, you could also QUOTENAME

WHILE (@7DaysEarlierPartitionIntegerId <= @CurrentPartitionIntegerId)
    BEGIN
        Set @7DaysEarlierPartitionId
            = CAST(@7DaysEarlierPartitionIntegerId AS char)
        set @sqlCommand
            = 'Select * from '
              + quotename(@RequestUsage_Partition + @7DaysEarlierPartitionId)
              + ' where UserLogin like ' + QUOTENAME('r2\rohit.kharade', CHAR(39))
        exec(@sqlCommand)
        set @7DaysEarlierPartitionIntegerId
            = @7DaysEarlierPartitionIntegerId + 1
    END


How to structure database in order to lookup and store post ID's in one PostgreSQL command? I have a table with user info (each user has a unique SERIAL ID), a posts table (each post also has a unique SERIAL ID, and references the ID of the user who posts it), a following table with two columns, each is a user id. Then there is a viewed table with a user id column and a post id column, so a user won't see a post twice.

I currently have an API endpoint /feed and it performs two PostgreSQL commands. The first one gets the last five most recent posts from the accounts that a user follows, but I want the user to not see a post twice, so next I store the ID's of the posts that are sent in the viewed table. I want this to be in one single PostgreSQL command. How could I achieve this?

you can use join table for example

select p.id, p.userid, p.content
from post p
join userinfo ui on p.userid=ui.id
order by created_date desc
limit 5

or you can use CTE feature (imagine two table as one table)

Reference
https://www.postgresql.org/docs/11/tutorial-join.html
https://www.postgresql.org/docs/11/queries-with.html


How can I use Invoke-SqlCmd with variables to output the SQL command only or some similar method? I have a big *.sql file that I run with Invoke-Sqlcmd and variables to do some tasks, and when I have an issue, it's a pain to debug because of the variables and I want to do logging of the SQL script about to be run.

Is there a reasonably native way to produce the final SQL about to be executed with variable substitution without actually running?

Here's a sample I run to restore a *.bak to various environments for example:

Invoke-Sqlcmd -InputFile $sqlFile -QueryTimeout 0 -ServerInstance 'SQLServer' -Database 'Master' -Variable @("DatabaseBAK=$TransactDB", "Database=$TargetDatabase") -Verbose

SQL File:

USE [master]

print 'Killing connections to database $(Database)'
DECLARE @kill varchar(8000) = '';

SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'
FROM sys.dm_exec_sessions
WHERE database_id  = db_id('$(Database)')
 and session_id <> @@SPID
EXEC(@kill);
GO
USE [master]

print '[Info] RESTORE DATABASE [$(Database)]'
print '[Info] FROM DISK = N''$(DatabaseBAK)'''
print 'Starting restore-------------------->'
RESTORE DATABASE [$(Database)]
FROM DISK = N'$(DatabaseBAK)'
WITH FILE = 1
    ,NOUNLOAD
    ,REPLACE
    ,STATS = 5
GO
print '<--------------------End restore'

I would like to somehow just output the above with variable substitution the way Invoke-SqlCmd does it.

I don't think invoke-sqlcmd can do thi.  Regular sqlcmd.exe has the -e parameter to output the batches, although it will not output the "GO"
.  EG

sqlcmd -e -i restore.sql -v database="foo" databaseBak="bk" > Restore.final.sql

You can put SET NOEXEC ON at the top, or just point it at an dummy instance to generate the script.

Or something like:

sqlcmd -e -Q "set noexec on;`n:r restore.sql" -v database="foo" databaseBak="bk"


How to solve - ORA-00205 I'm trying to create a multitenant database, but I get ORA-00205

I made a directory for datafiles:

[oracle@localhost oradata]$ mkdir -p TESTCDB/data/TESTCDB

I made an initTESTCDB.ora file:

db_name='TESTCDB'
enable_pluggable_database=true
audit_trail='db'
control_files='/u01/app/oracle/oradata/TESTCDB/data/TESTCDB/control01.ctl'
db_block_size=8192
db_domain=''
memory_target=629145600
memory_max_target=629145600
open_cursors=300
processes=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'

I set environment variables

[petar.testDB ~] vi .profile_TESTCDB
#!/bin/sh
umask 022
ORACLE_SID=TESTCDB; export ORACLE_SID
ORACLE_HOME='/u01/app/oracle/product/12.2/db_1'; export ORACLE_HOME
export PATH=$ORACLE_HOME/bin:$PATH

Now, I try to startup database in mount mode and I get this error:

SYS> startup mount
ORACLE instance started.

Total System Global Area  297795584 bytes
Fixed Size          8791960 bytes
Variable Size         234883176 bytes
Database Buffers       50331648 bytes
Redo Buffers            3788800 bytes
ORA-00205: error in identifying control file, check alert log for more info

Thanks in advance.
Petar

Creating a Database with the CREATE DATABASE Statement

If you want to create a database like that, then follow the steps listed on the above link.

Your next step should be Step 8: Start the Instance.

You have not created your the database yet, you do not have a controlfile, so you can not mount it. Startup the instance with nomount, then create the database with CREATE DATABASE.


Common Table Expression (CTE) benefits? From msdn :

  Unlike a derived table, a CTE can be self-referencing and can be
  referenced multiple times in the same query.

I'm using CTEs quite a lot, but I've never thought deeply about the benefits of using them.

If I reference a CTE multiple times in the same query:

Is there any performance benefit?
If I'm doing a self join, will SQL Server scan the target tables twice?

As a rule, a CTE will NEVER improve performance.

A CTE is essentially a disposable view.  There are no additional statistics stored, no indexes, etc.  It functions as a shorthand for a subquery.

In my opinion they can be EASILY overused (I see a lot of overuse in code in my job).  Some good answers are here, but if you need to refer to something more than once, or it's more than a few hundred thousand rows, put it into a #temp table instead and index it.


Installing Full Text Search to SQL Server 2008 R2 I installed sql server 2008 R2 Express successfully, then realised I need to get Full Text Search. So I downloaded the "Advanced Services Installation Package", but when I run it there is no option in the "Feature Selection" part for Full Text Search. Please don't tell me I hav to uninstall and reinstall?

Its a two step process.

In the installer select Maintenance/Edition Upgrade to upgrade your existing instance.
Run the installer again and select Installation/...or add features to an existing installation/Add features to an existing instance.


What is the SQL Server equivalent of pg_typeof(), how can I get the type of a column returned in a result set? Using PostgreSQL, I can use pg_typeof(exp) like
SELECT pg_typeof( cast(1 AS int) );
SELECT pg_typeof(x), x FROM foo;

And get back the result (integer in the case of the first example). Is there a SQL Server equivalent? How can I find the result given a field in a result set?
SELECT x
FROM foo;

I want to know what type x is?

There are various indirect methods, but nothing quite as simple as pg_typeof.
SQL_VARIANT_PROPERTY is often used for convenience, but has limitations since it cannot contain all other types.
Writing the subject of inquiry to a table then inspecting metadata is one method. Convenience system stored procedures like sys.sp_help can be employed as a somewhat more convenient method than writing a query against the metadata views.
Another, conceptually-related idea is to call:

sys.sp_describe_first_result_set (procedure); or
sys.dm_exec_describe_first_result_set (function)

For example:
EXECUTE sys.sp_describe_first_result_set
    @tsql = N'SELECT 1.0;'

SELECT FRS.*
FROM sys.dm_exec_describe_first_result_set
    (N'SELECT 1.0;', DEFAULT, DEFAULT) AS FRS;

These return a great deal of internal information, including type details. The above example returns:

system_type_name

numeric(2,1)

An alias type example:
CREATE TYPE Banana FROM binary(456) NOT NULL;

EXECUTE sys.sp_describe_first_result_set
    @tsql = N'DECLARE @b Banana; SELECT @b;'

SELECT
    FRS.system_type_id,
    FRS.system_type_name,
    FRS.user_type_id,
    FRS.user_type_schema,
    FRS.user_type_name
FROM sys.dm_exec_describe_first_result_set
    (N'DECLARE @b Banana; SELECT @b;', DEFAULT, DEFAULT) AS FRS;

returns (in part):

system_type_id
system_type_name
user_type_id
user_type_schema
user_type_name

173
binary(456)
260
dbo
Banana


Accessing a Linked server I am fairly new to the whole DBA scene. With that said, i was able to get an ODBC connection established within Microsoft SQL Server 2012 to be able to access a database for a library. Now what I am trying to do is create queries to be able to manipulate that said DB within Microsoft SQL Server but am having trouble finding ways to be able to do so. Can someone please help teach me how to call upon that Linked Server DB to be able to manipulate it as I see fit. Thank you in advance.

A linked server is referenced just like you would reference a database, with multi-part naming - LinkedSvrName.DBName.SchemaName.ObjectName.  There isn't really anything else to "accessing" a linked server.

There are some settings you will need to be cognizant of to ensure you can return data; RPC and RPC Out for instance.  Also you will want to use linked servers sparingly as performance will suffer.  This should get you well on your way to Googling the rest of what you need to know.  This topic is really too broad to be covered in this format.


Service Account for SQL Cluster I am setting up Test SQL ( 2012) server cluster with always on feature.

Cluster consist of two windows server 2012 R2 servers.

I need to setup SQL service accounts,

Does anyone know if it will be good idea to use same service accounts for both servers in cluster or separate service accounts ?

As per Microsoft, having same service account is the prerequisite :

All server instances that host an availability replica for the availability group must use the same SQL Server service account.
 The domain administrator needs to manually register a Service Principal Name (SPN) with Active Directory on the SQL Server service account for the virtual network name (VNN) of the availability group listener. If the SPN is registered on an account other than the SQL Server service account, authentication will fail.

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/prereqs-restrictions-recommendations-always-on-availability


I address this question back on Jun 08, 2012 : Update rank on a large table

First, make sure you have an index on rank and last_rank in the rank table

Next, make sure you have a points index on the score table

If you do not have such indexes, run the following:

ALTER TABLE rank  ADD INDEX (rank);
ALTER TABLE rank  ADD INDEX (last_rank);
ALTER TABLE score ADD INDEX (points);

You are going to have to update the last_rank on the fly using an UPDATE...JOIN:

SET @r=0;
UPDATE (SELECT id score_id FROM `score` ORDER BY points DESC) A
    LEFT JOIN `rank` B USING (score_id)
    SET B.last_rank = B.rank,B.rank = @r:= (@r+1)
;

Here is a more paranoid version

DROP TABLE IF EXISTS score_rank;
CREATE TABLE score_rank
(
    id        INT NOT NULL AUTO_INCREMENT,
    score_id  INT NOT NULL,
    rank      INT,
    last_rank INT,
    PRIMARY KEY (id),
);
SET @r=0;
INSERT INTO score_rank (last_rank,rank,score_id)
    SELECT B.rank,@r:= (@r+1),B.score_id FROM
    (SELECT id score_id FROM `score` ORDER BY points DESC) A
    LEFT JOIN `rank` B USING (score_id)
;
UPDATE rank A INNER JOIN score_rank B SET
    A.rank = B.rank,
    A.last_rank = B.last_rank
;
DROP TABLE IF EXISTS score_rank;

Give it a Try !!!

CAVEAT

If the table is very large, it may take time. As I suggested in Update rank on a large table, you may want to limit the ranking to the first 10,000 when ranking frequently, and then do the fuill ranking maybe once a week or once a month.

Therefore, ranking top 10,000 should look this

SET @r=0;
UPDATE (SELECT id score_id FROM `score` ORDER BY points DESC LIMIT 10000) A
    LEFT JOIN `rank` B USING (score_id)
    SET B.last_rank = B.rank,B.rank = @r:= (@r+1)
;

or this

DROP TABLE IF EXISTS score_rank;
CREATE TABLE score_rank
(
    id        INT NOT NULL AUTO_INCREMENT,
    score_id  INT NOT NULL,
    rank      INT,
    last_rank INT,
    PRIMARY KEY (id),
);
SET @r=0;
INSERT INTO score_rank (last_rank,rank,score_id)
    SELECT B.rank,@r:= (@r+1),B.score_id FROM
    (SELECT id score_id FROM `score` ORDER BY points DESC LIMIT 10000) A
    LEFT JOIN `rank` B USING (score_id)
;
UPDATE rank A INNER JOIN score_rank B SET
    A.rank = B.rank,
    A.last_rank = B.last_rank
;
DROP TABLE IF EXISTS score_rank;


pg_database_size() is small,but pg_dump is very big I faced a strange question.
when I use the psql command " \l+ db_name" or following sql,

cattle_dev=# \l+ cattle_dev
                                                    List of databases
    Name    |   Owner    | Encoding |   Collate   |    Ctype    | Access privileges |  Size   | Tablespace | Description
------------+------------+----------+-------------+-------------+-------------------+---------+------------+-------------
 cattle_dev | cattle_dev | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |                   | 1738 MB | pg_default |
(1 row)

cattle_dev=# select pg_size_pretty(pg_database_size('cattle_dev'));
 pg_size_pretty
----------------
 1738 MB
(1 row)

cattle_dev=#

it tells that the database is small.
But,if i use the pg_dump to backup this database,the result is too large,it's almost 18GB.

[enterprisedb@ppasdev 20170605012443]$ ll -h cattle_dev_20170605012443.dmp
-rw-rw-r--. 1 enterprisedb enterprisedb 18G Jun  5 01:34 cattle_dev_20170605012443.dmp
[enterprisedb@ppasdev 20170605012443]$

The problem is why the pg_dump result is too large,but the pg_database_size is small?

any help would be appreciated.thanks.

One of them is the binary size on disk. PostgreSQL compresses on disk all TOASTable fields over 2k (like text). Think about a binary value: they usually represent less space on disk than the same value CASTed to text anyway.

SELECT b::text, pg_column_size(b) AS on_disk, length(b::text) AS text
FROM ( VALUES (now()) ) AS t(b)
UNION ALL
  SELECT b::text, pg_column_size(b), length(b::text)
  FROM ( VALUES (49839489::int) ) AS t(b)
UNION ALL
  SELECT b::text, pg_column_size(b), length(b::text)
  FROM ( VALUES ('192.168.43.58'::inet) ) AS t(b);
               b               | on_disk | text
-------------------------------+---------+------
 2017-06-05 20:56:45.978472-05 |       8 |   29
 49839489                      |       4 |    8
 192.168.43.58/32              |      10 |   16
(3 rows)

In addition, you're going to have overhead merely because the SQL, schema, and input format (being CSV or whatever).

You may want to look into the -z option. (Or, xy if you need the best compression).

-Z 0..9
--compress=0..9
  Specify the compression level to use. Zero means no compression. For the custom archive format, this specifies compression of individual table-data
  segments, and the default is to compress at a moderate level. For plain text output, setting a nonzero compression level causes the entire output file
  to be compressed, as though it had been fed through gzip; but the default is not to compress. The tar archive format currently does not support
  compression at all.


How to lower "Created_tmp_disk_tables" - temporary tables count created on DISK? I have a ubuntu, mysql 5.6 DB that is heavy used by thousands of users. After 10 days of running I have this values:

| Created_tmp_disk_tables | 894170 |
| Created_tmp_files       | 26068  |
| Created_tmp_tables      | 914511 |

My config values are:

| tmp_table_size | 268435456 |
| max_heap_table_size | 1073741824 |

when I had tmp_table_size = 16MB and max_heap_table_size=16MB it was the same. Than I changed it to tmp_table_size = 256MB and max_heap_table_size = 1GB but Created_tmp_disk_tables did not stop growing. The rate is exactly the same.

I have 26GB heap. All tables are INNODB.

What am I missing?

Thanks

What do you mean by a "26GB Heap"?

tmp_table_size = 256M is dangerously high.  If multiple connections decide to need tmp tables, you could run out of RAM.  Swapping is worse for performance than lowering various settings.

Tmp tables are necessary in many situations.  Don't fear them.  But do check on them.

DISTINCT, GROUP BY, ORDER BY and UNION often require a tmp table.  If the tmp table fits in min(tmp_table_size, max_heap_table_size), then the tmp table may be in RAM using Engine=MEMORY.  If bigger than that, then the tmp table is Engine=MyISAM and is slower.  There are other reasons for using MyISAM, most notably is selecting a TEXT field.  More details.

Another common "error" is to blindly use VARCHAR(255) and utf8.  When using MEMORY, that becomes a 765-byte CHAR, which hastens the conversion to MyISAM.

The numbers you gave...

Created_tmp_disk_tables is nearly as large as Created_tmp_tables -- This implies that either tmp_table_size is not big enough (which I doubt), or MEMORY cannot be used (which I suspect).  Above 20% is a red flag in my analysis.
Created_tmp_tables / Uptime -- above 20/second is a red flag
Created_tmp_disk_tables / Uptime -- above 1/second is a red flag
Created_tmp_disk_tables / Questions -- above 4% is a red flag

Set long_query_time = 1 and turn on the SlowLog, preferably to FILE.  Wait a day, then use pt-query-digest on the slowlog to find the "worst" queries.  If you can't figure out how to improve them, ask us.

I'll disagree with Rolando on two items:

RAM disk is a bad idea -- it is better to give the extra RAM to other caches rather than trying to outsmart MySQL.
OPTIMIZE TABLE is rarely useful, and not very relevant for the question.


Quickest way to NULL or wipe a column I have a unique situation, where I need to anonymize a column quickly. By that I mean any way to remove the data, whether it is NULL'ing, blanking or something else. There can be up to 20 million records to update and there are no indexes on the column I am updating.

I have tried a couple of things, for instance:

Update TABLE Set COLUMN = NULL

This clearly is the worst performing query. I have modified it to exclude any current NULLs or blanks from that set, but it is still very slow.

I have tried to drop and re-create the column, which so far is instant. But unfortunately because of business reasons, the column order is important, so this ruins that. (For the purpose of the question assume reordering columns is impossible).

Recently I tried to change the column type to char(1), then back to text - which had better performance. However, after seeing it run lightning fast with a drop and re-create column, I am curious if there is a way to do this with keeping the column order intact. Clearly SQL Server is capable of producing ~20 million records as NULLs instantly - there must be a clever way around this?

First let's do a quick review of how SQL Server generates the default ordering of columns when you do a SELECT * query. There are probably a few edge cases but I believe that columns are returned in the order that they are created. If a column is dropped the ordinal ID associated with that column is not used and cannot be used by a new column.

Consider this sample table:

CREATE TABLE dbo.SEE_COLUMN_ORDER (
COL1 INT,
COL2 INT,
COL3 INT,
COL4 INT
);

We can see the column_id using this query against sys.columns:

SELECT name, column_id
FROM sys.columns
WHERE object_id = OBJECT_ID('SEE_COLUMN_ORDER');

Initial results:

╔══════╦═══════════╗
║ name ║ column_id ║
╠══════╬═══════════╣
║ COL1 ║         1 ║
║ COL2 ║         2 ║
║ COL3 ║         3 ║
║ COL4 ║         4 ║
╚══════╩═══════════╝

Now drop a column:

ALTER TABLE dbo.SEE_COLUMN_ORDER DROP COLUMN COL3;

New results:

╔══════╦═══════════╗
║ name ║ column_id ║
╠══════╬═══════════╣
║ COL1 ║         1 ║
║ COL2 ║         2 ║
║ COL4 ║         4 ║
╚══════╩═══════════╝

Now add a column:

ALTER TABLE dbo.SEE_COLUMN_ORDER ADD COL3 INT;

New results:

╔══════╦═══════════╗
║ name ║ column_id ║
╠══════╬═══════════╣
║ COL1 ║         1 ║
║ COL2 ║         2 ║
║ COL4 ║         4 ║
║ COL3 ║         5 ║
╚══════╩═══════════╝

There is no column with a column_id of 3. As far as I can tell there is no known or supported way of making COL3 have a column_id of 3. If you wanted that column to be displayed third in a SELECT * query you could define a view against the table with the name and a different schema or you could drop and recreate the entire table with the column order that you want.

Recreating an entire table sounds like a slow operation but sometimes it can be quicker than updating all of the rows for a single column. It'll depend on your system, your table structure, and the data in your table. One scenario in which the drop and create might be faster is if your transaction log writes are the bottleneck. With a recovery model of simple you can create the new table with minimal logging which will write very little data to the transaction log compared to the UPDATE. Another scenario in which the drop and recreate can be faster is if the UPDATE will lead to a lot of page splits. It's possible to construct a table and an UPDATE which splits every data page into two (an UPDATE to a column value making it NULL won't do this as far as I know). As a general rule of thumb, the same amount of data is saved to the log regardless of the recovery model unless the operation can be minimally logged. UPDATE is never minimally logged so switching to simple won't reduce the transaction log requirements for that operation.

To cover the part of the question that deals with performance, one important thing to note is that adding and dropping a column to an existing table are optimized operations that have a fixed cost that doesn't scale with the amount of data in the table. To see this in action I'm going to look at how much data is logged in the transaction log for the operations (dropping and adding columns can be rolled back in a transaction). I'm testing against SQL Server 2016.

Here is the sample data:

DROP TABLE IF EXISTS dbo.X_COLUMN_WIPE_2;
CREATE TABLE dbo.X_COLUMN_WIPE_2 (
    ID INT NOT NULL IDENTITY (1, 1),
    COL_TO_WIPE VARCHAR(1) NULL,
    FILLER VARCHAR(100) NULL,
    PRIMARY KEY (ID)
);

-- 2536 rows
INSERT INTO dbo.X_COLUMN_WIPE_2 WITH (TABLOCK)
SELECT 'A', REPLICATE('Z', 100)
FROM master..spt_values t1;

Per sys.dm_tran_database_transactions, the UPDATE writes 324752 log bytes to the transaction log:

BEGIN TRANSACTION

UPDATE dbo.X_COLUMN_WIPE_2 SET COL_TO_WIPE = NULL;

ROLLBACK;

Dropping and adding a column writes just 1992 log bytes to the log:

BEGIN TRANSACTION

ALTER TABLE dbo.X_COLUMN_WIPE_2 DROP COLUMN COL_TO_WIPE;

ALTER TABLE dbo.X_COLUMN_WIPE_2 ADD NEW_COLUMN VARCHAR(1) NULL;

ROLLBACK;

Now to test with more data:

DROP TABLE IF EXISTS dbo.X_COLUMN_WIPE_3;

CREATE TABLE dbo.X_COLUMN_WIPE_3 (
    ID INT NOT NULL IDENTITY (1, 1),
    COL_TO_WIPE VARCHAR(1) NULL,
    FILLER VARCHAR(100) NULL,
    PRIMARY KEY (ID)
);

-- 6431296 rows
INSERT INTO dbo.X_COLUMN_WIPE_3 WITH (TABLOCK)
SELECT 'A', REPLICATE('Z', 100)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

The same UPDATE now writes 721979808 bytes to the transaction log but dropping and creating a column still writes just 1992 bytes.

It's possible to dig into the internals of why this happens using the undocumented DBCC PAGE. Here's an example from when I ran the code on my system (copying and pasting the code won't work because the page numbers will be different):

DROP TABLE IF EXISTS dbo.X_COLUMN_WIPE_4;
CREATE TABLE dbo.X_COLUMN_WIPE_4 (
    ID INT NOT NULL IDENTITY (1, 1),
    COL_TO_WIPE VARCHAR(1) NULL,
    FILLER VARCHAR(100) NULL,
    PRIMARY KEY (ID)
);

INSERT INTO dbo.X_COLUMN_WIPE_4 WITH (TABLOCK)
SELECT 'A', REPLICATE('Z', 100)
FROM master..spt_values t1;

-- first first data page
DBCC IND('SE_DB',X_COLUMN_WIPE_4,-1)

-- view first data page
DBCC TRACEON(3604)
DBCC PAGE('SE_DB',1,1192232,3);

BEGIN TRANSACTION

UPDATE dbo.X_COLUMN_WIPE_4 SET COL_TO_WIPE = NULL;

DBCC PAGE('SE_DB',1,1192232,3);

ROLLBACK;

As Paul White would say I'm quite the amateur when it comes to this stuff, but I'll give my interpretation anyway. Here's a diff of what's in the page for the first record in the table before and after the update:

I underlined what I suspect are the important pages in red. Note that many of the physical lengths have changed and that the value "A" for COL_TO_WIPE is no longer present in the row data. It looks like the UPDATE changed much of the data stored in the page.

Here's a diff between the original table and after the COL_TO_WIPE column is dropped:

The number of differences is much smaller than before. None of the row data is changed. I believe that there is just a metadata operation performed on the table outside of the table pages, so all of the changes displayed by DBCC PAGE here are logical as opposed to physical.

Here's a diff between the original table and after the column is dropped and a new column is added:

As before there don't really appear to be any physical differences. The actual data looks to be the exact same. There are just column definitions with a physical length of 0.

Since the problem here appears to be related to how the date is stored row by row in pages, perhaps the results would be different for tables created as clustered columnstore indexes which are stored in column format. After all, it is reasonable to think that updating a column value should only affect the one column instead of all of the data in the table. Unfortunately for this case, the current implementation of UPDATE against CCIs is a logical delete and insert. So if you update all of the rows for a column to be NULL, SQL Server will mark all of the existing rows in the table as logically deleted and will create new rowgroups for all of the rows.

In summary, other than recreating the whole table you've already mentioned all of the "clever" ways in the question that I know of for working around your problem. It's very unlikely that there is an "instant" way to update a column to be NULL in the middle of the table without breaking the previous column order in the table.


Help to understand explain plan in Oracle I am running a query in some big tables, and although it runs fine even tough is a lot of data, I'd like to understand what part of it weighs on the execution. Unfortunately I am not too good with explain plans so I call for help.

Here is some data about these tables:

history_state_table 7.424.65 rows (of which only 13.412 are left after t1.alarm_type = 'AT1' )
costumer_price_history 448.284.169 rows
cycle_table 215 rows

This would be the query (don't mind the logic, is just for the reference):

SELECT t1.id_alarm, t2.load_id, t2.reference_date
  FROM history_state_table t1,
       (SELECT   op_code, contract_num,
                 COUNT (DISTINCT id_ponto) AS num_pontos,
                 COUNT
                    (DISTINCT CASE
                        WHEN vlr > 0
                           THEN id_ponto
                        ELSE NULL
                     END
                    ) AS bigger_than_zero,
                 MAX (load_id) AS load_id,
                 MAX (reference_date) AS reference_date
            FROM costumer_price_history
           WHERE load_id IN
                            (42232, 42234, 42236, 42238, 42240, 42242, 42244) /* arbitrary IDs depending on execution*/
             AND sistema = 'F1'          /* Hardcoded filters */
             AND rec_type = 'F3'         /* Hardcoded filters */
             AND description = 'F3'      /* Hardcoded filters */
             AND extract_type IN
                    ('T1', 'T2', 'T3')
        GROUP BY op_code, contract_num) t2
 WHERE t1.op_code = t2.op_code
   AND t1.contract_num = t2.contract_num
   AND t1.alarm_type = 'AT1'
   AND t1.alarm_status = 'DONE'
   AND (   (    t1.prod_type = 'COMBO'
            AND t2.bigger_than_zero = t2.num_pontos - 1
           )
        OR (    t1.prod_type != 'COMBO'
            AND t2.bigger_than_zero = t2.num_pontos
           )
       )
       /* arbitrary filter depending on execution*/
   AND t1.data_tratado BETWEEN (SELECT data_inicio
                                  FROM cycle_table
                                 WHERE id_ciclo = 160) AND (SELECT data_fim
                                                              FROM cycle_table
                                                             WHERE id_ciclo =
                                                                           160)

And finally the explain plan:

Plan
SELECT STATEMENT  ALL_ROWSCost: 5,485
    13 NESTED LOOPS
        7 NESTED LOOPS  Cost: 5,483  Bytes: 115  Cardinality: 1
            5 VIEW  Cost: 12  Bytes: 59  Cardinality: 1
                4 SORT GROUP BY  Cost: 12  Bytes: 85  Cardinality: 1
                    3 INLIST ITERATOR
                        2 TABLE ACCESS BY INDEX ROWID TABLE RAIDPIDAT.COSTUMER_PRICE_HISTORY Cost: 11  Bytes: 85  Cardinality: 1
                            1 INDEX RANGE SCAN INDEX RAIDPIDAT.IDX_COSTUMER_PRICE_HISTORY_2 Cost: 10  Cardinality: 3
            6 INDEX RANGE SCAN INDEX RAIDPIDAT.IDX_HISTORY_STATE_TABLE_1TPALM Cost: 662  Cardinality: 102,068
        12 TABLE ACCESS BY INDEX ROWID TABLE RAIDPIDAT.HISTORY_STATE_TABLE Cost: 5,471  Bytes: 56  Cardinality: 1
            9 TABLE ACCESS BY INDEX ROWID TABLE RAIDPIDAT.CYCLE_TABLE Cost: 1  Bytes: 12  Cardinality: 1
                8 INDEX UNIQUE SCAN INDEX (UNIQUE) RAIDPIDAT.PK_CYCLE_TABLE Cost: 0  Cardinality: 1
            11 TABLE ACCESS BY INDEX ROWID TABLE RAIDPIDAT.CYCLE_TABLE Cost: 1  Bytes: 12  Cardinality: 1
                10 INDEX UNIQUE SCAN INDEX (UNIQUE) RAIDPIDAT.PK_CYCLE_TABLE Cost: 0  Cardinality: 1

Mind that I am not asking "how to rewrite it more efficiently", but rather how do I find witht the explain plan what the most costly operation there. Meanwhile I am reading about it, but I'd appreciate some help.

Explain plan does not tell you what is actually the most costly "operation". The "Cost" column is a guess - it is a value estimated by optimizer. So is "Cardinality" column and "Bytes" column. http://docs.oracle.com/cd/B28359_01/server.111/b28274/ex_plan.htm#i18300

In your example, your optimizer tells you:  I decide to use this plan because I guess that looping would cost about 5,483. And I hope this would be the most costly part of the execution, but I can't guarantee this.

The same applies recursively to all the depths of the tree.

If you go in-depth to the lowest levels (that is by intuition most-looped, most-executed levels) you see that the operation that especially sticks out, both in terms of expected cost and expected number of elements, is the

6 INDEX RANGE SCAN INDEX RAIDPIDAT.IDX_HISTORY_STATE_TABLE_1TPALM Cost: 662  Cardinality: 102,068

So, optimizer guessed that optimal execution of this query is to loop a lot around a poor workhorse RAIDPIDAT.IDX_HISTORY_STATE_TABLE_1TPALM. I really cannot see which part of your query directly relates to it, but I suspect t1.data_tratado condition. And, again, I cannot see if it is really the most costly part.

I'll try to translate the syntax of loops in the explain plan to procedural pseudo-code:

/* begin step 13 (by "step 13" I mean a line that reads "   13 NESTED LOOPS") */
  /* begin step 7 */
    do step 5
    myresult = rows from step 5
    for each row from myresult {
       do step 6
       for each row from step 6 {
           join to a row from myresult the matching row from step 6
       }
    }
  /* end step 7 */
  for each row from myresult {
     do step 12
     for each row from step 12 {
         join to a row from myresult the matching row from step 12
     }
  }
/* end step 13 */
return myresult

Seems complicated, but really aim of each "nested loop" is to create a join (a single table made of two tables) in the most naive way, a loop-inside-a-loop.


Using an AWS database that can store more than 65,535 bytes in a row I have a MySQL Barracuda table with about 150 fields and most of them are TEXT types.

If I wanted to store more than 65,535 characters in a row, would it be possible to do so using Amazon's AWS database ? If so which kind of database ? RDS ?
I am using a semi-VPS kind-of hosting company which has the application in vanilla PHP connected to this database. Would it be possible to migrate just the database to AWS and keep the application running on my hosting company ? (I should be able to connect to this AWS database from my host's PHP application.

(Addressing Q1)

There are many workarounds for having bloated columns.  Let's tackle that.

What is the table's ROW_FORMAT?  DYNAMIC and COMPACT work differently.

Do they all need to be TEXT?  Or can some be VARCHAR with low limits?  (Eg: name TEXT --> name VARCHAR(60))

Consider "vertical partitioning".  This is where you split a table into 2 tables -- putting some columns in each table.  They would have the same PRIMARY KEY (except not both being AUTO_INCREMENT).

Do any of the columns have repeated values?  If so, consider normalizing.  (Eg, country TEXT -> country_code CHAR(2) CHARACTER SET ascii with values like 'US', 'ES', 'UK', 'IN'.)

Have you splayed an array across columns?  That is a no-no.  Turn that set of columns into rows in a related table.  (Eg home_phone, work_phone, fax, cell.)

Please provide SHOW CREATE TABLE with the actual column names; we may have more hints.  (And some of my suggestions above may be painfully obvious.)

After looking at the schema...

I see a bunch of columns that sounds like money or other numeric stuff, but is in VARCHAR(25); consider changing to INT or some other numeric datatype

I see about 80 each of TEXT and VARCHAR.  This does stress some builtin limits in InnoDB.  But they can, and need to be, worked around:

Plan A:  Vertical partitioning -- break out FR, COP, PP, Customer, etc. into a few extra tables.  Use JOIN to re-gather the data.

Plan B:  Throw most of the columns into a large JSON text column.  Or maybe one for FR, one for Customer, etc.  Most app languages can easily parse JSON.

Since you have only one index (on id) I guess there is no call for searching the table?  A few other columns, like maybe userID and name, should be kept in their own columns, and possibly indexed.


Is there any reason not to use foreign keys in MySQL? I've never really used foreign keys before but I am building a project where they could come in handy. I have looked at some other large software such as some forum systems and they don't use foreign keys, so I'm wondering why wouldn't they!?

I understand that it would come in handy for situations where they have the username in the users table and then on the posts table for example they have the users username in the author field rather than an id (my assumption is they do this to avoid expensive joins to the users table); so for this situation you would think adding a foreign key to the posts table to reference the username would be a good idea.

I'm wondering - when you do something such as this:

// Start transaction
SELECT * FROM users WHERE username='some_username' LIMIT FOR UPDATE;
// End transaction

Because of the foreign key placed on the posts table with reference to the username in the users table, does that then cause not only that row in the users table to be locked but also all rows in the posts table that reference the users username to be locked as well?

If not, are there any other reasons why one would not use foreign keys for instances such as this?

Because of the foreign key placed on the posts table with reference to the username in the users table, does that then cause not only that row in the users table to be locked but also all rows in the posts table that reference the users username to be locked as well?

If you are concerns about transaction (insert, update, delete)
Locking will occur when Mysql is trying to update some rows. In your situation, chances are you'll either update the user table OR the post table.
The fastest updates or deletes are done using primary keys.
PK in innodb are clustered (physically ordered) so it is very fast.
If you were to update a post for a user, you would first grab the user_id (select user_id from user where username =?). You would then update the post later (update post set .. WHERe userid = x). I would avoid the select for Update.
I would encourage you to use innodb for transactional application. Not only it supports FK, but support row locking and point in time recovery (to name a few).

If you system will be 90% select, few updates, or data warehouse style, then I would recommend Myisam.


Check for spaces or zeros in all columns in all tables I have multiple tables in an Oracle schema that have columns with incorrect values:

Number columns where the value is zero, instead of null.
Text columns where the value is a space, instead of null.

I’m in the process of cleaning up the data. I will eventually create constraints on the columns to prevent bad data being entered in the first place. But before I do that, I need to find the columns that have existing problems and investigate them.
To start, I would like to produce a list of columns that have incorrect values.

For each table in a schema, provide a list of columns that have zeros or spaces as values.

table_name    column_name    datatype    value    count
—————————————————————————————————-————————————-———————-
roads         width          number      0        500
sidewalks     description    varchar2    [space]  10000
sidewalks     const_year     number      0        2000

Question:
What would it take to produce a list like this in Oracle?
(Note: I’m a public works technician by day, not a database administrator. Layman’s terms would be appreciated.)

You should look into querying the information schema based tables (such as ALL_TAB_COLUMNS or DBA_TAB_COLUMNS) to get a list of all tables and all columns: https://docs.oracle.com/en/database/oracle/oracle-database/18/refrn/ALL_TAB_COLUMNS.html#GUID-F218205C-7D76-4A83-8691-BFD2AD372B63
https://docs.oracle.com/en/database/oracle/oracle-database/18/refrn/DBA_TAB_COLUMNS.html#GUID-91F945AE-5778-45A6-A07E-775A006A9AA1
Then look into how to write dynamic SQL to easily generate the code you need to query every column of every table: https://docs.oracle.com/en/database/oracle/oracle-database/18/lnpls/dynamic-sql.html#GUID-7E2F596F-9CA3-4DC8-8333-0C117962DB73
Here is some psuedo-code on what your thought process should be when creating the dynamic SQL (*note this is not Oracle PL/SQL syntax, so just take it as a general example on how to build dynamic SQL, and focus mostly on step 3):
-- 1) Create a temp table to hold the results
DROP TABLE IF EXISTS #Results
CREATE TABLE #Results (TableName VARCHAR(100), ColumnName VARCHAR(100), [Value] VARCHAR(100) NULL)

-- 2) Dynamic SQL variable used to build the dynamic SQL string
DECLARE @DynamicSQL AS NVARCHAR(MAX) = ''

-- 3) Build the dynamic SQL string using the DBA_TAB_COLUMNS information schema view. First query gets any row whose column has 0 as a value, second query gets any row whose column has a NULL value
SELECT @DynamicSQL = @DynamicSQL + '
    INSERT INTO #Results
    SELECT ''' + TABLE_NAME+ ''' AS TableName, ''' + COLUMN_NAME + ''' AS ColumnName, [' + COLUMN_NAME + '] AS ColumnValue
    FROM [' + TABLE_NAME+ ']
    WHERE [' + COLUMN_NAME + '] = ''0'';'
    + '
    INSERT INTO #Results
    SELECT ''' + TABLE_NAME + ''' AS TableName, ''' + COLUMN_NAME + ''' AS ColumnName, [' + COLUMN_NAME + '] AS ColumnValue
    FROM [' + TABLE_NAME + ']
    WHERE [' + COLUMN_NAME + '] IS NULL;'
FROM sys.DBA_TAB_COLUMNS

-- 4) Execute the dynamic SQL string
EXEC sp_ExecuteSQL @DynamicSQL

-- 5) Select the final results, grouping up on TableName, ColumnName, and Value to get the counts of 0 and NULL values per column per table
SELECT R.TableName, R.ColumnName, TC.DATA_TYPE AS DataType, R.[Value], COUNT(1) AS [Count]
FROM #Results AS R
INNER JOIN sys.DBA_TAB_COLUMNS AS TC
    ON R.TableName = TC.TableName
    AND R.ColumnName = R.ColumnName
GROUP BY R.TableName, R.ColumnName, R.[Value]


Getting "unknown variable postgresqlini" when updating PostgreSQL I am getting an error when attempting to upgrade a PostgreSQL installation. The full error is:

  A data directory is registered in ***unknown variable postgresqlini*** but is neither empty or a recognized data directory

I've already had a look at this issue, which seems to be the only other one I can find which is related, but it did not help me. As you can see, my paths are surrounded by quotation marks.

Here is the full command that is run (separated to multiple lines to make it readable)

"C:\ProgramData\Package Cache\44103BBE02C9C99CB868F4767B0CC3B5D50B6C09\postgresql-10.11-1-windows-x64.exe"
--unattendedmodeui minimal
--mode unattended
--servicename RM_Postgresql
--servicepassword xyz
--superpassword xyz
--prefix "C:\Program Files\\company\postgres"
--datadir "C:\Program Files\\company\data"
--disable-components stackbuilder

This command is part of an installer which launches the PostgreSQL installer. I've been running the same command for nearly 2 years without any issues (using different versions of PostgreSQL on different computers). For some reason, it seems to now be failing on one of our machines, and I am unsure as to why.

The currently installed version of PostgreSQL on the computer where it is failing is 10.6-1, and I am attempting to update to 10.11-1 if that makes any difference.

This now seems to cause issue with the previous installation as well:

initializeDatabase fail on opening postgres msg=could not connect to server: Connection refused (0x0000274D/10061)
    Is the server running on host "localhost" (::1) and accepting
    TCP/IP connections on port 5432?
could not connect to server: Connection refused (0x0000274D/10061)
    Is the server running on host "localhost" (127.0.0.1) and accepting
    TCP/IP connections on port 5432?
QPSQL: Unable to connect

Same issue here while installing postgresql-12.4-1-windows-x64.exe

A data directory is registered in ***unknown variable postgresqlini*** but is neither empty or a recognized data directory

After deleting the folder: c:\Program Files\PostgreSQL\12\, the installation went through.


Incorrect syntax in adventure works install script via SMSS Im trying to install a sample DB by script. Ive tried with pubs, northwind and now adventureworks. I get a lot of errors. Im posting here because i believe im doing something wrong. Im assuming the install script for adventureworks is properly scripted still i get lots of error messages like

> Msg 102, Level 15, State 1, Line 39
Incorrect syntax near ':'.

Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)
    Aug 22 2017 17:04:49
    Copyright (C) 2017 Microsoft Corporation
    Developer Edition (64-bit) on Windows 10 Home 10.0 <X64> (Build 17134: )

Started - 2018-07-07 15:15:49.647

*** Dropping Database
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '$'.

*** Creating Database
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '$'.

It looks like you are running the script from an SSMS window and it contains SQLCMD variables. Enable SQLCMD mode from the Query menu (Query-->SQLCMD Mode). Be sure the SQLCMD variable values are set as desired before running the script.


How can I instantly update a table in a database with the columns values from another database? I have database_1 and database_2 both on the same server.

How can I instantly update the USER table in database_2 when data is inserted into the USER table in database_1?

Structure of both tables:

CREATE TABLE USER
(
    id int
    , email varchar(256)
    , `name` varchar(1000)
    , `address` varchar(1000)
);

Please help me with all steps to do this.

You can probably use a trigger to accomplish your goal.

Consider the following simplified example:

CREATE TABLE t1
(
    c1 int
    , c2 int
);

CREATE TABLE t2
(
    c1 int
    , c2 int
);

CREATE TRIGGER t1_trigger AFTER INSERT ON t1
FOR EACH ROW
BEGIN
    INSERT INTO t2 (c1, c2)
    VALUES (New.c1, New.c2);
END;

INSERT INTO t1 (c1, c2)
VALUES(1, 2);

Check the results:

SELECT *
FROM t2;

╔═════╦════╗
║ c1  ║ c2 ║
╠═════╬════╣
║  1  ║  2 ║
╚═════╩════╝

I've created a simple fiddle that shows the working code, and allows you to play with it.

The above creates two tables, the first with a trigger that copies rows inserted into t1 into t2.  In the above example, both tables are in the same database... since you are attempting to copy from one database to another, the following syntax might work in the trigger if both databases are on the same MySQL Server:

CREATE TRIGGER t1_trigger AFTER INSERT ON t1
FOR EACH ROW
BEGIN
    INSERT INTO other_database.t2 (c1, c2)
    VALUES (New.c1, New.c2);
END;


Error while restoring a Database from an SQL dump I am  extremely new to MySQL and am running it on Windows. I am trying to restore a Database from a dumpfile in MySQL, but I get the following error:

$ >mysql -u root -p -h localhost -D database -o < dump.sql
ERROR: ASCII '\0' appeared in the statement, but this is not allowed unless option --binary-mode is enabled and mysql is run in non-interactive mode. Set --binary-mode to 1 if ASCII '\0' is expected. Query: 'SQLite format 3'.

I tried $ > mysql -u root -p -h localhost -D database --binary-mode -o < dump.sql but this gave me the following ERROR at line 1: Unknown command '\☻'.
It is a 500 Mb dump file, and when I view its contents using gVIM, all I can see is expressions and data which is not comprehensible. Also when I try to copy contents from the file to post here all I can copy is :SQLite format 3 This kind of seems strange.

The reference to --binary-mode (introduced in MySQL 5.6.3) is probably a distraction.

It doesn't sound like you're dealing with a mysqldump output file, there.  Try the file utility.

shell> file dumpfile.sql
dumpfile.sql: ASCII text

If you don't get the ASCII text response, you're dealing with either something that isn't a dump file from mysqldump at all, or you're dealing with something that's been compressed (with gzip or bzip2, for example), which you'd need to uncompress before piping it into mysql.

If you see SQLite 3.x database then you definitely have your answer... it's a raw SQLite database, not a MySQL dump file.

Indeed, the first few bytes of a SQLite database are these:

53 51 4C 69 74 65 20 66  SQLite f
6F 72 6D 61 74 20 33 00  ormat 3^@

Note that the 16th octet here is 0x00, explaining the ERROR: ASCII '\0' appeared in the statement... message in this case.  The suggestion that --binary-mode is appropriate is a false alarm.

Windows users: the 'file' utility is a tool from Unix, but the Windows version can be found here.


Specific disadvantages to storing infrequently accessed large files in a large database I have heard many times across the web that it is, for various reasons, a bad idea to store files directly in a database, the preferred method being to store filenames in the database, and files directly on the filesystem. Most explanations, however, seem to assume that the stored files (in my case 2MB longtext object containing XML) are important to retrieve along with the query. I've seen a few comments in passing, however, that suggest that by storing these files directly in your database you are bloating it and thereby "interfere with IO or query performance of the rest of your database."

Under what circumstances does this happen, and why?

It is my understanding that blob and longtext over a certain size were stored externally anyways. Does the database still attempt to load up the entire longtext of an entry when it's respective row is used in operations? My main concern here comes from the fact that most of the queries of the table containing these rows of longtext do not actually need the longtext xml for anything. Does simply having these cumulatively massive chunks of data sitting in the same table really affect the performance of other queries against that table?

InnoDB Architecture

This first thing that comes to mind is the InnoDB Buffer Pool. It holds three things

Data Pages from Accessed .ibd files
Index Pages from Accessed .ibd files
Insert Buffer for Changes to Secondary Indexes

Any query accessing infrequent data will basically remove the working dataset from the InnoDB Buffer Pool just to load the adhoc data. To get those data back, you have two options:

OPTION 1 : Reread the data and index pages from disk. You are certainly going to experience a cache storm  that will impact Server Load with Disk I/O, Memory Utilization, and possible swapping.
OPTION 2 : Unload Buffer Pool Before Accessing Old Data and Reload Buffer Pool Afterwards. This is a new feature for MySQL 5.6. Apparently, it was made for occasions like this. Here are the options for loading the InnoDB Buffer Pool at Startup, Shutdown and On-Demand :

innodb_buffer_pool_dump_now
innodb_buffer_pool_dump_at_shutdown
innodb_buffer_pool_load_now
innodb_buffer_pool_load_at_startup

EPILOGUE

OPTION 1 is something you reluctantly live with until all subsequent queries bring back your most recent working dataset
OPTION 2 requires scripting adhoc reports to do the following

Backing up the InnoDB Buffer Pool
Run the adhoc queries on the infrequent data
Restoring the InnoDB Buffer Pool

OPTION 2 requires upgrading to MySQL 5.6
There are other ways MySQL and InnoDB are impacted. I discussed these in my recent post How to optimize MySQL for large BLOB updates


Locale is incompatible error in Postgresql 10 when replicating from Ubunu to Win 10 The locale available in my Ubuntu PG-10 DB is en_US.UTF-8. When doing PG_Basebackup to a PG-10 DB in Win-10, Psql failed to connect with error

  psql: FATAL:  database locale is incompatible with operating system
  DETAIL:  The database was initialized with LC_COLLATE "en_US.UTF-8",
  which is not recognized by setlocale(). HINT:  Recreate the database
  with another locale or install the missing locale.

The locale available in Win10 are

                          List of databases
   Name    | Owner      | Encoding | Collate | Ctype |
-----------+------------+----------+---------+-------+-
 postgres  | postgres   | UTF8     | en-US   | en-US |

                                           List of databases
   Name    | Owner    | Encoding |          Collate           |           Ctype            |
-----------+----------+----------+----------------------------+----------------------------+
 postgres  | postgres | UTF8     | English_United States.1252 | English_United States.1252 |

But the above locale are not taking when doing the Basebackup. Any fix for this issue.

You can't copy a data directory (which is what pg_basebackup does) between two different operating systems. The missing locale is just one symptom. If you need to replicate between Linux and Windows use logical replication – a_horse_with_no_name


Can I have cross database or cross server references through a relative path? I have two databases, db1 and db2, running on two different servers, server1 and server2, and db2 has some stored procedures that will modify the table columns on db1.

In these stored procs, we use Linked Servers and fully qualified names to refer to the tables on db1, like [server1].[database].[dbo].[table1].

Now, we decided to migrate db1 from server1 to server2. After migration, db1 and db2 will both run on server2.

Questions

Do I have to modify each single cross server reference to remove the [server1] name in the stored procs?
Could we define cross DB or cross server reference through relative path, so we don't need to modify them every time the two databases change server?

Thank you.

Assuming that, post migration, you have absolutely no other need for this Linked Server to Server 1, then you should be able to drop and recreate it with the same name but pointing to the current / local instance (i.e. Server 2). This is known as a "loop back" and will allow the code to continue working unmodified.

HOWEVER, that is a short-term solution as you don't want to go through a Linked Server if you don't need to. You should go through all of the code and remove the [Server1]. references. This loop back Linked Server is just a means of buying time to do these changes slowly rather than needing to have it all done up front.