Why does MySQL produce so many temporary MYD files? On a Debian Linux server, hosting many PHP/MySQL websites (photo galleries), sometimes I have "many" files like /tmp/#sql_6405_58.MYD.

For example today :

[2012-12-15 15:18:11] /tmp/#sql_6405_6.MYD : 88MB
[2012-12-15 15:18:11] /tmp/#sql_6405_3.MYD : 22MB
[2012-12-15 15:18:11] /tmp/#sql_6405_4.MYD : 138MB
[2012-12-15 15:18:11] /tmp/#sql_6405_10.MYD : 88MB
...
[2012-12-15 15:18:11] /tmp/#sql_6405_9.MYD : 15MB
[2012-12-15 15:18:11] /tmp/#sql_6405_65.MYD : 49MB
[2012-12-15 15:18:11] /tmp/#sql_6405_44.MYD : 69MB

(59 files at the same time, for more than 6GB... yes I monitor big files in /tmp)

Unfortunately, /tmp is on the same partition than / and it temporary breaks the web server, because / is full I suppose. Then files disappear and the server is back to normal.

All the file names follow the #sql_6405_*.MYD pattern. I would like to understand which MySQL operation implies so many temporary files. I have approximately 2000 databases on this server. Is it possible to know which database is concerned?

There are some options that can cause temp tables to materialize as MyISAM tables or can be configured to delay it. Keep in mind that for disk-based temp tables, there are no .frm files, but only .MYD and .MYI files (of course. the .MYI file is never used since it is impossible index an internal temp table).

Here are the options:

Temp Table Variables That Should Be the Same Size

tmp_table_size
max_heap_table_size

sort_buffer_size
join_buffer_size

You should also consider the MySQL Documentation on Internal Temp Table Usage

The situations where in-memory temp tables are made are

If there is an ORDER BY clause and a different GROUP BY clause, or if the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue, a temporary table is created.
DISTINCT combined with ORDER BY may require a temporary table.
If you use the SQL_SMALL_RESULT option, MySQL uses an in-memory temporary table, unless the query also contains elements (described later) that require on-disk storage.

When an in-memory temp table exceeded the minimum of (tmp_table_size or max_heap_table_size), mysqld does the following:

Suspends the query
Copies the in-memory table's contents into a MyISAM temp table
Discards the in-memory table
Continues the query, sending the temp data into the MyISAM temp table

The situations where in-memory temp tables are bypassed in favor of disk are

Presence of a BLOB or TEXT column in the table
Presence of any column in a GROUP BY or DISTINCT clause larger than 512 bytes
Presence of any column larger than 512 bytes in the SELECT list, if UNION or UNION ALL is used

Some due diligence is required to reduce temp table creation on disk

Setting join_buffer_size bigger
Setting sort_buffer_size bigger
Setting tmp_table_size and max_heap_table_size bigger
Tuning queries to minimize or even prevent temp tables
Creating indexes to create presorted view of data from individual tables
Installing additional RAM to accommodate large in-memory temp tables

If after such due diligence, there are still temp tables being formed on Disk, here is one desperate move: Mapping disk-based temp table creation to memory.

Here is a quick-and-dirty way to set up a 16GB RAM Disk using tmpdir

STEP01) Create RAM Disk Folder

mkdir /var/mysql_tmpfs

STEP02) Add this to my.cnf

[mysqld]
tmpdir=/var/mysql_tmpfs

STEP03) Add this to /etc/fstab

echo "none /var/mysql_tmpfs tmpfs defaults,size=16g 1 2" >> /etc/fstab

STEP04) Reload /etc/fstab

mount -a

STEP05) service mysql restart

After this, all temp table that become MyISAM are written to the RAM Disk. This should speed disk-based temp table creation.

Give it a Try !!!


mongodb conf node won't start - compatibility error I recently upgraded a dev sharded-cluster from 3.6 to 4.0 - one of the conf servers is now failing to start:

  2020-01-30T13:19:02.972-0600 F  CONTROL  [initandlisten] ** IMPORTANT:
  UPGRADE PROBLEM: Found an invalid featureCompatibilityVersion document
  (ERROR: BadValue: Invalid value for version, found 3.6, expected '4.2'
  or '4.0'. Contents of featureCompatibilityVersion document in
  admin.system.version: { _id: "featureCompatibilityVersion", version:
  "3.6" }. See
  http://dochub.mongodb.org/core/4.0-feature-compatibility.). If the
  current featureCompatibilityVersion is below 4.0, see the
  documentation on upgrading at
  http://dochub.mongodb.org/core/4.0-upgrade-fcv.

The upgrade from 3.6 -> 4.0 reported as successful - I checked the data shards but didn't think to check each conf server... Today, I just upgraded the mongo binaries and on reboot, encountered the problem with one of the conf servers failing to start with the above mentioned error.  The cluster had been running at 4.0 for weeks before I noticed this problem following the update reboot.

The other two conf servers (running) are reporting that they're at 4.0 so life is good there, as are all of the data shards.

I cannot start the conf server without encountering this error which shuts-down the server making it impossible to issue the set-feature-compatibility-version directive from mongos.

Since the other two conf nodes are running and reporting the correct release version, would it be best to just nuke the down-server's data, attempt to restart the node, and then issue the command to ensure that the restarted node's data version is correct?  Or, is there some sort of force command that would bypass the version check?

TIA!

Went with the nuclear option:

stopped the orphaned server
moved the data dir and recreated new dir
removed orphaned server from primary
started orphaned server with null data dir
added orphaned server to pool from primary

data started sync'ing immediately and rs.status() for the cluster config servers reporting all is well.


Error Code: 1845. ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE I upgraded to MySQL 8.0.13 and tried to add a new field using ALGORITHM=INSTANT but got the following error:

  Error Code: 1845. ALGORITHM=INSTANT is not supported for this
  operation. Try ALGORITHM=COPY/INPLACE

Then I recreated the same table in MySQL 8.0.13 and tried to add the same field using ALGORITHM=INSTANT and it works!

Will this new feature of MySQL (ALGORITHM=INSTANT) only work on newly created tables in the upgraded version?

According to my research, this is a bug

https://bugs.mysql.com/bug.php?id=92194

which is explaining
 " For ALTER TABLE, ALGORITHM=INSTANT was incorrectly rejected on tables
created in a MySQL version prior to 8.0.12 "
" Fixed in 8.0.14. "

I didn't tested it with 8.0.14. I will post here the results once I tested it with 8.0.14.

[update]

Tested it in 8.0.14 and it works


MySQL 5.6 Aborted_connects increase without error log MySQL server version: 5.6.26
Linux: Linux 2.6.32-431.11.7.el6.ucloud.x86_64 #1 SMP Wed Jan 7 23:42:27 CST 2015 x86_64 x86_64 x86_64 GNU/Linux
Situation:

Aborted_connects increase 5-10 every minutes
log_warnings=2, but no error/warnings in error files
using TCPDUMP 3306 port, but cannot find any aborted connection request.

server status like below:

# Variable_name, Value
Aborted_connects, 7991
Connection_errors_accept, 0
Connection_errors_internal, 0
Connection_errors_max_connections, 0
Connection_errors_peer_address, 3
Connection_errors_select, 0
Connection_errors_tcpwrap, 0
Connections, 8051
Max_used_connections, 24
Performance_schema_session_connect_attrs_lost, 0
Ssl_client_connects, 0
Ssl_connect_renegotiates, 0
Ssl_finished_connects, 0
Threads_connected, 22

some referred server variables like below:

connect_timeout 10
max_allowed_packet  4194304
max_connect_errors  100
table_open_cache    2000
Connection_errors_accept    0
Connection_errors_internal  0
Connection_errors_max_connections   0
Connection_errors_peer_address  3
Connection_errors_select    0
Connection_errors_tcpwrap   0

at least the reason was a ZABBIX setting for mysql template wrong.
Does somebody know why the ZABBIX_AGENT of current server connect error were not be writed in the error log of mysql when log_warning level is 2?

Aborted_connects/Connections is nearly 1.  This is extremely high.

Threads_connected is always 1 or more because "1" is the connection that is doing the SHOW STATUS.  Max_used_connections = 2 probably does mean that two connections were connected at some time.  It is a high-water-mark.


Keep Execution Plan in Cache How can I keep a query execution plan in SQL Server's cache and use it several times?

When I execute a query for the first time it takes 7 seconds. After that it takes only 1 second.  This query is running once on daily basis.

Is there any way I can keep the execution plan and use it every day?

AFAIK, you cannot force a plan to stay in cache. However, a query can be thrown out of the cache for several reasons. Read a blog about execution plans. It states some reasons why execution plans get invalidated:

  Changing the structure or schema of a table referenced by the query
  Changing an index used by the query
  Dropping an index used by the query
  Updating the statistics used by the query
  Calling the function, sp_recompile
  Subjecting the keys in tables referenced by the query to a large number of inserts or deletes
  For tables with triggers, significant growth of the inserted or deleted tables
  Mixing DDL and DML within a single query, often called a deferred compile
  Changing the SET options within the execution of the query
  Changing the structure or schema of temporary tables used by the query
  Changes to dynamic views used by the query
  Changes to cursor options within the query
  Changes to a remote rowset, like in a distributed partitioned view
  When using client side cursors, if the FOR BROWSE options are changed

Another pitfall could be that the query changes. This can happen if the where clause changes (e.g. you filter by date) and you don't use bind

Thinking about, you haven't stated that you checked whether the execution plan is still in cache or not. You should query the cache to figure that out. However, since you say your query runs only once a day, the plan might just be expired. The blog actually mentions the formula on how sql server determines when to expire a plan:

  If the following criteria are met, the plan is removed from memory:

  More memory is required by the system
  The "age" of the plan has reached zero
  The plan isn't currently being referenced by an existing connection

In general, 7 seconds is not to bad if you have a big report. It takes longer to get a coffee. Does it create any other issue than 6 seconds of wasted productivity for an end user?


Microsoft Access is giving me network interrupted errors We have just upgraded to window 10 and are using Microsoft Access 2016 (16.0.8431.2110) 32 bit.

As a result of this upgrade we now have the issue of intermittent network connectivity issues. My Access database session work will just lose the network connection, tables will be corrupted and the following error message will be displayed:

  Your network access was interrupted. To continue, close the database, and then reopen it again.

Needless to say this is problematic as we are using 2016 AutoPLANT SS8 v8i with AutoCAD 2016 Service Pack 1 that is constantly reading/writing to the database. The result is a file crash and a reload is necessary.

Any one out there in the world have an idea of what is happening?

Let's have a look at the individual components.
Microsoft Access
According to Microsoft the Office program Access does have some limitations in regards to using it on network drives:

There are several ways that you can share an Access database depending on your needs and resource availability.

Reference Ways to share an Access desktop database (MS Office Support)
...but specifically:

Share data by using network folders
This is the simplest option and has the least requirements, but also provides the least functionality. In this method, the database file is stored on a shared network drive, and all users share the database file simultaneously. Some limitations include reliability and availability if there are multiple simultaneous users changing data since all database objects are shared. This technique can also reduce performance as all the database objects are sent across the network.

(Emphasis mine)
Reference: Share data by using network folders (MS Office Support)
Bentley AutoPLANT Modeler SS8 Refresh
The support notes for your versions are listed as:

This build supports

Windows 7, Windows 8.1 and Windows 10 x64
Office 2010, 2013 and 365/2013 and 365/2016 (both 32bit and 64bit)
SQL Server 2008 R2 / 2012 SP2 / 2014 (Enterprise / Standard / Express Edition)
ORACLE 11g Release 2 (v11.2.0.3) / 12c Release 1 (v12.1.0.2)
OpenPlant Isometrics Manager (OPIM) V8i (SELECTseries 6) Refresh
AutoPLANT i-model Composer V8i (SELECTseries 8) Refresh is required
The Run-time version of Microsoft Access is not supported
The Professional version of Office (including Microsoft Access) is required:

Enable use of Bentley Project Administrator.
For the generation of reports via Data Manager, Vision and Document Manager etc.
For use of Advanced Selection dialog in AutoPLANT Modeler and AutoPLANT Isometrics.

Reference: AutoPLANT Modeler SS8 Refresh - Installation Sequence (Bentley Communities)
Combining The Two Bits of Information

If you are using Access for the intended purpose, then I would recommend opening up a support ticket with Bentley to determine what the issue might be.

If you are using Access for the central database (which should be running on Microsoft SQL Server or Oracle 9i RDBMS), then I would recommend to re-install your current environment.

If Access remains the culprit, then you might have to check your Company's Microsoft Domain Policies. You might have a rule that is automatically disconnecting your network drives after a certain period of inactivity.

If your network drives are indeed failing, then you might want to contact the infrastructure guys to have a look a the SAN disks (where your network drives ar situated) and/or the network department to determine if your network is dropping connections:

after a period of time
due to network connectivity issues

If you have a support contract with Microsoft then open up an incident with Microsoft.


Select the oldest records in a table in Oracle please, how can I select the oldest records in a table in oracle? and if it is possible to show them by date.
Thank you

Oracle does not, itself, track the date a row is inserted into a table. So unless you yourself design the table with something like a DATE_INSERTED column, and reigorously enforce the proper population of that column, then the answer to "how can I select the oldest records in a table in oracle?" is "you can't".


Keeping bank transactions in sync I'm counting on your expertise in this one, I'm developing a scrapper for banking transactions history.
My problem is, scrapping should be done regularly and the bank doesn't supply any unique identification field for each transaction, thus I need to find a way of not importing already imported data..
I can't greedly import if not exists because outliers might ruin the reliability of the application:

1) "01-01-2014", "ABCDEFG", "+100,00", "+1024,56"
2) "01-01-2014", "ABCDEFG", "-57,00", "+967,56"
3) "01-01-2014", "ABCDEFG", "-43,00", "+924,56"
4) "01-01-2014", "ABCDEFG", "+100",   "+1024,56"

Here the 1st and 4th are different operations, that should both be imported into the db. I'm probably looking for a solution that takes into account the whole imported data sequence.
Any ideas on how to implement this, possibly efficiently?! I'm using postgresql, here's my table's DDL's:

CREATE TABLE "extrato" (
    "extrato_id"  VARCHAR(4) PRIMARY KEY,
    "linha"    INTEGER NOT NULL
);

CREATE SEQUENCE linha_extrato_seq;

CREATE TABLE "linha_extrato" (
    "linha_id" INTEGER PRIMARY KEY DEFAULT nextval('linha_extrato_seq'),
    "dt_mov" DATE NOT NULL,
    "dt_val" DATE NOT NULL,
    "descricao" VARCHAR(60) NOT NULL,
    "quantia" NUMERIC(10,2) NOT NULL,
    "saldo" NUMERIC(10,2) NOT NULL,
);
ALTER SEQUENCE linha_extrato_seq OWNED BY linha_extrato.id;

As Craig Ringer said the lack of a timestamp field or unique identifications might ruin the reliability of the application, so I'm doing the best i can to lessen the impact of the "incomplete" data I have.
An important detail is that the synchronization process is to be done frequently, say the last sync was in day 1, then the second sync will take into account the transactions from day 1 and forth, thus overlaps if existent will always happen in the last synced date.

Thus far I tough about the following procedure:

FOR first_row to last_row in to_insert
    IF !exists(cur_row)
        insert([cur_row,remaining_rows]);
        break;
    END IF
END FOR

Any way this can be implemented in PL/pgSQL? can i process the set of inserts as a unique operation?

It probably isn't possible to do this robustly.

If your previous sequence ends in:

"01-01-2014", "ABCDEFG", "+100,00", "+1024,56"

and the next sequence begins with:

"01-01-2014", "ABCDEFG", "+100",   "+1024,56"

is that the same transaction as before? Or is the data really:

"01-01-2014", "ABCDEFG", "+100",   "+1024,56"
"01-01-2014", "ABCDEFG", "+100",   "+1024,56"

so you should import two transactions?

Without a timestamp, some useful unique transaction identifier, or the ability to know for sure what point you've already imported up to (say, transactions being split into discrete and non-overlapping monthly statements) I don't think you can do this reliably.


How to install oracle 11g XE and use the data files of another instance (like Postgres)? I installed Oracle 11g XE in Windows 7 x64, then Windows 8.1 (install new OS, not perform an upgrade of the previously Windows 7 x64 installation). Can be possible to install Oracle 11g XE over the existing directory to use the database that was created with Windows 7 like Postgres do when the Data Directory exists?

I see no reason you can't do this.

Typically, you would perform the following steps.

Move the *.dbf files found in %ORACLE_BASE%\oradata\ to a safe place.
Move the files found in %ORACLE_BASE%\fast_recovery_area\ to a safe place.
Move the spfile found in %ORACLE_HOME%\database\spfileXE.ora to a safe place.
Install a new Oracle instance and
Configure it with oracle-xe configure command.
Shut the instance down.
Move the data files, control file, spfile and online redo log files back to their previous locations.
Star the instance.

You would also need to make sure that you specified the same settings during installation as you used with your previous database (ORACLE_HOME, ORACLE_SID, listener port etc.), otherwise your instance won't start. Ideally I would recommend you doing silent installation with response files (described in Oracle Database Express Edition Installation Guide for Microsoft Windows). This would ensure that you've used the same settings across operating system upgrades/reinstall.


How to connect to a Postgres server in a Docker (1.12.0) container on a Mac? The latest release of Docker doesn't use a virtual machine anymore, instead using a hypervisor to connect to the containers. This means I can no longer login to postgres with psql:

➜  postgres git:(master) ✗ docker run -d -p 5433:5432 db postgres
<sha>
➜  postgres git:(master) ✗ docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                    NAMES
111f3bed4c52        db                  "/docker-entrypoint.s"   17 minutes ago      Up 17 minutes       0.0.0.0:5433->5432/tcp   zen_hugle
➜  postgres git:(master) ✗ psql -p 5433 -U postgres
psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/tmp/.s.PGSQL.5433"?

I have also tried specifying localhost as the host, but that results in a strange output:

➜  postgres git:(master) ✗ psql -h localhost -p 5433 -U postgres
psql: %

Does anyone know what to do in this case? Thank you.

I had the same problem today. I discovered that you do have to specify the -h flag for it to work:

psql -U postgres -p 5433 -h localhost

You can also use the URI style connection string:

psql postgresql://postgres:<your_password>@localhost:5433

The default is "local socket" which does not work in the case of docker.


Count values to show up with zeros instead of null I'm trying to make a query where I'll have the id and the number of employees that person is their superior, also people that aren't managers, should appear and have a count of zero.

This is what I did but those that aren't managers show up with a count of null:

create view scnt as (
    select E1.super, count(distinct E1.id) cnt
    from Employee E1 left outer join Employee E2
    on E1.super = E2.id
    group by E1.super
);

create view numSuper as (
    select E.id, S.cnt
    from Employee E left outer join scnt S
    on S.super = E.id or E.id = null
);

select * from numSuper

Here
That's the schema:

but we can ignore all the noise and use just what we need: employee(id, super)

Here's a fiddle with the inserts: http://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=4c08869d0e8eba6c82fd4957092379a1

Any ideas on how to do this?

You need to join in the opposite way: managers LEFT JOIN employees

create view scnt as
    select E2.id as super,
           count(E1.id) as cnt
    from Employee E2                    -- managers
      left outer join Employee E1       -- employees
      on E1.super = E2.id
    group by E2.id
  ;

select * from scnt ;

dbfiddle


Count missing values across columns and join back to original table Here is the table:

I want to count the missing values across each row for t1, t2, t3,..., and create another column in the same table with the results as shown in the picture.

I can easily do this in something like python pandas. But on SQL (specifically BigQuery and/or postgreSQL, MySQL), I can't seem to figure out the syntax. Here is my attempt:

select
  array_agg(id),
  array_agg(date),
  count(t)
from
(
  select id, date, t1 as t from testdata
  union all
  select id, date, t2 as t from testdata
  union all
  select id, date, t3 as t from testdata
) as data
group by id, date, t

Any ideas where I am going wrong? I think union all unpivots the wide to long table, but how do I count the missing values within a specific range and join all the results back to the original table?

SELECT id, date, t1, t2, t3
     CASE WHEN t1 IS NULL THEN 1 ELSE 0 END
     + CASE WHEN t2 IS NULL THEN 1 ELSE 0 END
     + CASE WHEN t3 IS NULL THEN 1 ELSE 0 END AS missing_across_all_cols
   FROM testdata


How to back out of mass update in SQL Replication We have a database which is set up in Merge Replication.

We accidentally updated hundreds of thousands of records in a large published table on the publisher database. We did something like this:

update LargeTable
set Field = Field

So basically, every record got updated to be the same - no data was actually changed. However, every record in the table is now being delivered to every subscriber and the whole system has come to a halt. We are very worried this will take days and days to complete.

How can we safely remove data from the msmerge_contents and msmerge_genhistory table? Are there other system replication tables to remove records from? I can see the exact records in these tables that refer to the mass update. I just don't want to remove them without knowing it is safe to do so.

There is no supported way to achieve what you want to do, aside from complete reinitialization. That said, you might be able to achieve a shortcut and retain a supported configuration, but only under direction of a Microsoft support engineer. Attempting to modify the system replication tables without that guidance risks compounding your current problems.

My advice would be to open a case under your support contract, or take the hit of reinitializing and change operating practices to prevent a recurrence of the problem in future.


One to one mapping vs many to one mapping on data base design I'm currently doing call detail records (CDR) data analysis as my project. On course of data-base design, I had separated the date (table contain id, day,month,year), time field (table contain id, second, minute, hour etc) from call table (table contain called number, duration, time_id, date_id etc) as millions of call transaction occurs in a day. Now I got confuse on how to map date, time to call table i.e. do one-to-one mapping or many-to-one mapping. One to one mapping can be done easily but data are redundant but I have no idea about many-to-one mapping. What I think about many to one mapping is I have first extract date and check if it exist or not. if exit then get its id otherwise insert date and get its id. (Background:- I have a cdr data with field card_no, service_key, calling_no, called_no, answer_time, clear_time, duration  on text file on tab separated format and have to make data mart as like figure below.

I am not good on telling problem so if any information required then comment below.

EDIT

On cdr table

Card_no is mobile number of the subscriber and if the subscriber dialing to the other number than if is equal to calling and if subscriber is receiving the call then card_no is equal to called. In call table its repeating.
sql_time_stamp means answer_time and as duration is already in database then why I have to store clear_time also ( if there is any reason then let me know)

On Subscriber table

Subscriber table contain dob which is static so I create another table  as this contain age (which may be changing) just to separate static part and dynamic part also gender is include to reduce multiple table joining as analysis just contain age(_group) and gender.

Some observations on this data model based on some assumptions of your intent. Your cdr data is a tab-delimited text file. The cdr data becomes the central transaction table. A transaction table has a large number of rows that change often. It looks like there are other lookup tables surrounding the call table. A lookup table has a small number of records that do not change often. I think it is good that you have a separate field, a unique integer, as the key to each table. Integer keys are the best for indexing.

Calls

Create an autogenerated key on this table similar to the others. Then put a unique index on [calling party key], [date key], [time of day key]. It is important to avoid using dates and times as keys.
Add [card no] field. Is this a unique identifier of a cdr record? If so,
use it as the new primary key.
Remove foreign key to subscriber demographics.
Add a [call date] field.
Remove the foreign key to Date table.
Add a [holiday indicator] field. One character.
Does the [SQL Date-Time Stamp] mean [answer time]?
Add a [time interval] field.
Remove the foreign key to Time of Day.
Add [peak time indicator] field. One character.
Add [call length classifier] field.
Remove foreign key to Derived Call information.

Subscriber
The [subscriber key] and [subscriber id] look like either could be a key. Do you need both?

Subscriber Demographics
It looks like a subscriber demographic record depends only on the subscriber, not on the call detail.

Move the relationship from subscriber demographics to subscriber
instead.
Add foreign key to Subscriber.

Date
It is unusual to store all possible dates in a table this way. The individual components of a date (day, month, year, day of week, and weekend) can easily be computed. Generate them in a query. Maybe save the query or view. Do not store those attributes physically. The date table is not needed.

Store the date and holiday indicator in the Calls table.
The holiday indicator would be a 1 byte field.
Remove the Date table.

Time
It is unusual to store all possible time including seconds in a separate table.

Move the time and interval to the calls table.
Remove the time table.

Mobile Service
This looks like a lookup table.

Rate Plan
This looks like a lookup table.

Calling Party
This looks like this gets populated from the cdr records. And then the activation date is updated on the record.

Derived Call Information

Option 1 if derived call information is mostly filled. This looks like it depends only on the call.

Move the [peak time indicator] and [call length classifer] fields to
the calls table.
Then you can remove this table.

Option 2 if derived call information is mostly empty. You can save space in the Calls table by keeping the derived call table.

Add [card no] as the primary key. It is a 1 to zero-or-1 relationship. Every derived call must belong to a call. Not all calls have derived calls.

Edit: removed Calls.[clear time].


Pivot a table to repeat the first column for each following column i'm not sure how to explain so I'll try and write out an example.

I'm currently using an access database with a table in the following format

1 A B C D
2 A B C D

I'm trying to perform a query that will provide me with an output in the following format:

1 A
1 B
1 C
1 D
2 A
2 B
....

Currently this will be performed on a Access Db but later on it will need to be converted to a SQL Server query.

I'm not that fluent in SQL so any help would be welcome :)

Thanks!

This will work wit Access and SQL-Server

SELECT ID, A AS Fieldx
FROM tbl
UNION ALL
SELECT ID, B
FROM tbl
UNION ALL
SELECT ID, C
FROM tbl
UNION ALL
SELECT ID, D
FROM tbl;

specific for SQL-Server:

SELECT ID, FieldX FROM
   (SELECT ID,A,B,C,D   FROM tbl) p
UNPIVOT
   (FieldX for dummyID in (A,B,C,D)) as dummyField

Additional information can be found here


Is Policy-Based Management dead? Is it scalable? Policy-Based Management was introduced in SQL Server 2008.

I'm looking for software/tool I can use to enforce configuration standards across a SQL Server 2012 /2014 database environment.  Instances are in the hundreds & globally distributed.

Is PBM the right tool to use? It has great out of box functionality, but i fear it will not scale & will not be future-proof.

Other end of the spectrum is Powershell DSC.  Which is the more 'roll your own' approach. Admittedly there are 'experimental' scripting resource kits.

Are there other tools which provide a good middle ground?

I liked it and have not seen it being removed anywhere so far.  I used PowerShell DSC since the beta and it's great but it is no replacement for PBM.  DSC is great for enforcing system states and some SQL states.  I can use it to roll out automated testing environments and ensure all the domain membership, firewalls, users, restored databases, masked data, shares, etc. are all done.

Then PBM would be used to help out in areas that I want a report to meet compliance requirements, actually enforcing state on the fly.  DCS has a check in time where it scans, and it requires you to write custom scripts to test and apply changes if the test fails, it also requires a central server.

I like both but for very different purposes and PBM can be very helpful if deployed as part of a greater effort.


Mongo 3 Intermitted Crashes Currently running two VMs with fresh installs of Ubuntu 14.04 x64 and Mongo 3 Stable. Each VM has the same specs with 8 cores and 56GBs of RAM. Our processing VM is doing great under heavy loads, however our production VM seems to have Mongo crashing once every few hours and can't pinpoint why or how.

My config for the production Mongo instance looks like this

Here is the last three logs outputted. The first two links are cut near the bottom as they were too big, and the last is a full log top to bottom (as it was a short lived run).

https://gist.github.com/dustinblackman/e32778344ec5f21af0d8
https://gist.github.com/dustinblackman/acb0a3aef77114275de4
https://gist.github.com/dustinblackman/908d63e7bd386aae6bc7

The applications that are communicating with this instance are using the latest PHP, Python, and NodeJS drivers.

Does anything in the logs stand out to anyone? All that I see is that the only proper operation that happens before a crash is auth (and I've tried disabling auth, but no deal). Any help is appreciated. Thanks!

Open up /var/log/messages and search for mongo.

If you see "oom-killer" or "Out of memory" then this points to your problem.

We had that problem when running a mongos and multiple mongods on the same machine, for testing purposes.

Since there is no way to limit mongo's memory usage, we just added a 4GB swapfile, so there was some leeway when memory was at its limit.  (Using swap is the recommendation here.)

More on the OOM killer here.


Custom modules for plpythonu I'm writing a series of plpythonu functions which share a few common bits of code. I'd like to package the shared code into a separate module for ease of maintenance. Is there an easy way to call my custom module from plpythonu?

I can't guarantee access to locations in Python's path so dropping it in a folder on the path may not work.

These are all part of a PostgreSQL extension so ideally I'd install the helper Python module along with the other extension files.

You might be able to do something along the lines of what was done in check_updates-1.0.1, if you can't make guarantees about being able to install in the system's PYTHONPATH.

In the Makefile, there is a substitution of  based off the output of pg_config for the use lib directive of Perl. You could do something similar like this:

import sys
sys.path.append("<LIBDIR>/myextension")

in the Makefile of your extension to ensure that the install directory for modules is in the PYTHONPATH in your functions, no matter where someone has things installed.

You can cache your imports too, as referenced in this StackOverflow question: How are import statements in plpython handled, which should help with performance too.

It's not "easy", though, sadly. We have to wait for future improvements to plpythonu to make such things easier.

Hope that helps answer your question. =)


Sync Indexes between two tables I need to automate and Sync only indexes between two tables ( Primary and Stage_Table) within the same database.

I tried using SSIS SQL Server Objects Task, but looks like it works only when we sync between two different instances. Indexes on Primary table keep getting changed and I need the empty stage_table with all the updated indexes when I do the partition switch.

You can use opensource tool like sql-dbdiff or OpenDBDiff. Both are commandline, so can be used in automating scripts.

Also, if you want 3rd party licensed tool then Redgate's SQL Compare (if u want for data compare -- there is data compare as well)  is very useful and I have used it extensively for automation.

Out of curiosity, why do you need Indexes on Staging table as a staging table is meant for temporary loading data and then after cleaning it, the data gets loaded in the primary table ?

EDIT :

Based on your need, best will be to use SQL Server Partition Management Tool from Codeplex.

This utility provides a command line interface to:
1. Remove all the data from one partition by switching it out to a staging table. It creates the required staging table.2. Create a staging table for loading data into a partition. The staging table can be created with or without indexes -- if created without indexes this utility provides a separate command to create appropriate indexes on the staging table, before SWITCHing it into the partitioned table.
The commands can be invoked from other scripts for end-to-end sliding window scenarios. Using the utility allows you to avoid maintaining partition maintenance scripts that must remain synchronized with index or column changes in the permanent table, since necessary staging objects can be created on-demand.


Restoring 40 GB Oracle backup to get db objects into Oracle 11g XE I have backup of oracle database size is almost 40 GB.This 40GB database backup contains lots of data that I don't care about. I just wanted to restore DB objects like tables structure, procedures, functions, triggers etc.
limitation: I have only Oracle 11G Express Edition(Free) which has limitation of only 10 GB.

is there any way to restore db objects from database backups?

In case of expdp/impdp backup

impdp ... CONTENT=METADATA_ONLY

This will import only the DB objects, but no data. Should you require some data, you can get them for example on table, tablespace, or schema level:

impdp ... CONTENT=DATA_ONLY TABLES=my_table,my_table_2...
impdp ... CONTENT=DATA_ONLY TABLESPACES=tablespace_1,tablespace_2...
impdp ... CONTENT=DATA_ONLY SCHEMAS=schema_1,schema_2

In case of exp/imp backup

You can try partially importing the tables or tablespaces (syntax same as with impdp):

imp ... TABLES=(jones.accts, scott.emp, scott.dept)
imp ... TABLESPACES=tablespace_1,tablespace_2...

In case of rman backup

From this answer on partial restore from RMAN backup:

  RMAN is a physical backup - it copies files - and not surprisingly is restricted to physical restoration. As a consequence, its supported granularities are: block, file, tablespace and database.

So if you are lucky, and can get by with restoring a tablespace, it might work if there are different tablespaces in the backup and the one you need fits into the 10GB limit.

Using Oracle 11g EE

If you have access to a full Oracle 11g (beware, the development license prohibits using it for internal data processing), just import the full DB, and do a selective export if none of the above is successful.


Cleaning up specific domain logins on about 50 servers and therefore the users There is a specific need to clean up logins from the SQL Servers after migration from one domain say abc to new domain foo.

After migration as we still have those old logins and users under naming of abc\login1, abc\login2... and so on.

How can I perform this clean up to delete all the logins from the server, which has Windows logins say with domain abc\.. and within same script to delete all the related users within the instance for all databases.

I've searched the link SQL Server script to delete accounts no longer in Active Directory but it does not help in deleting users.

Note: These are just Windows logins and not part of any AD group. Just want to drop logins and the db users associated with it. Other domain AD users have been taken care by our AD and wintel team and users are all set. Just doing the clean up here.

I have had a similar task recently. My solution:

Create a temporary stored procedure #sp_helplogins which is a copy of sp_helplogins but returns only the second rowset.
Get logins to delete (in my case by using sp_validatelogins)

DECLARE @Login  sysname, @SQL   NVARCHAR(4000)

CREATE TABLE #BadLogins
(
    [SID]       varbinary(85)
    ,NTlogin    sysname
)

CREATE TABLE #BadUsers
(
    LoginName       VARCHAR(100)
    ,DBName         VARCHAR(100)
    ,UserName       VARCHAR(100)
    ,UserOrAlias    VARCHAR(100)
    ,IsDeleted      INT
)

INSERT #BadLogins
EXEC sp_validatelogins

Get associated users

DECLARE myCursor CURSOR READ_ONLY
FOR
SELECT DISTINCT NTlogin
FROM #BadLogins

OPEN myCursor

FETCH NEXT FROM myCursor INTO @Login

WHILE @@FETCH_STATUS = 0 BEGIN

INSERT #BadUsers
EXEC #sp_helplogins @Login

FETCH NEXT FROM myCursor INTO @Login

END

CLOSE myCursor
DEALLOCATE myCursor

Drop the users

DELETE #BadUsers
WHERE DATABASEPROPERTYEX(DBName, 'Updateability') != 'READ_WRITE'

WHILE EXISTS ( SELECT 1 FROM #BadUsers WHERE IsDeleted = 0 AND UserOrAlias = 'User' ) BEGIN

SELECT TOP 1 @SQL = 'USE ' + DBName + '
DROP USER ' + QUOTENAME(UserName)
FROM #BadUsers
WHERE IsDeleted = 0
    AND UserOrAlias = 'User'
ORDER BY UserName,DBName

EXEC sp_executesql @SQL
--SELECT @SQL

UPDATE u
SET IsDeleted = 1
FROM #BadUsers u
    JOIN (
        SELECT TOP 1 DBName, UserName
        FROM #BadUsers
        WHERE IsDeleted = 0
            AND UserOrAlias = 'User'
        ORDER BY UserName,DBName
    ) d ON d.DBName = u.DBName AND d.UserName = u.UserName
WHERE IsDeleted = 0

END

Notice that I don't handle a case when a user have an owned schema so the users won't be deleted by my script.


Galera: any drawback of enabling wsrep_log_conflicts and cert.log_conflicts? We have a HAProxy which balances reads and writes to only one (of three) Galera nodes.

If this balancing works fine, the parameters wsrep_log_conflicts and cert.log_conflicts should never log any conflicts and errors? It won't fill up /var/log filesystem with messages?

What is the drawback of enabling wsrep_log_conflicts and cert.log_conflicts? Why it's not enabled by default?

Please give more info, the only thing I found was DEALING WITH MULTI-MASTER CONFLICTS

  Lastly, you can enable conflict logging features through
  wsrep_log_conflicts and cert.log_conflicts.

# Enable Conflict Logging
wsrep_log_conflicts=ON
wsrep_provider_options="cert.log_conflicts=YES"

  These parameters enable different forms of conflict logging on the
  database server. When turned on, the node logs additional information
  about the conflicts it encounters, such as the name of the table and
  schema where the conflict occurred and the actual values for the keys
  that produced the conflict.

7:51:13 [Note] WSREP: trx conflict for key (1,FLAT8)056eac38 0989cb96:
source: cdeae866-d4a8-11e3-bd84-479ea1a1e941 version: 3 local: 1 state:
MUST_ABORT flags: 1 conn_id: 160285 trx_id: 29755710 seqnos (l: 643424,
g: 8749173, s: 8749171, d: 8749171, ts: 12637975935482109) <--X-->
source: 5af493da-d4ab-11e3-bfe0-16ba14bdca37 version: 3 local: 0 state:
APPLYING flags: 1 conn_id: 157852 trx_id: 26224969 seqnos (l: 643423,
g: 8749172, s: 8749171, d: 8749170, ts: 12637839897662340)

This information may not be of interest to the DBA. If the application is handling conflicts correctly, there is no need to log them. It would be expected behaviour. You would only enable this level of logging if you were debugging a specific issue. Otherwise it would generate a lot of spam in the logs.


Temp table lives after being explicitly dropped I'm creating a post-deployment script in VS2017 database project.
In this script, I'm using single temp table, which is re-created frequently to adjust to different data structures.

Below you can see a sample excerpt:

    SELECT
        *
    INTO #tmp
    FROM dbo.SourceTable1
    WHERE
        1 = 0

    drop table #tmp

    SELECT
        *
    INTO #tmp
    FROM dbo.SourceTable2
    WHERE
        1 = 0

Unfortunately, when I execute this code, I receive an exception:

Msg 2714, Level 16, State 1, Line 15 There is already an object named '#tmp' in the database.

It looks like the DROP statement is not working here as expected to.
Do you have any idea why does it behave like this?

Try putting a batch separator (GO) command between the SELECT/DROP/SELECT

GO signals the end of a batch of Transact-SQL statements

SELECT *
INTO #tmp
FROM dbo.SourceTable1
WHERE 1 = 0

GO

DROP TABLE #tmp

GO

SELECT *
INTO #tmp
FROM dbo.SourceTable2
WHERE 1 = 0


Indexing for query containing xml column I have below queries which performs little slow.So i am planning to create index.I created primay xml index and now performance improved little bit(by looking at execution plan) .Queries are shown below.

Update account set   [dailybalance].modify('replace value of   (/Root/Row[date=''2013-02-04'']/Balance/text())[1] with   (/Root/Row[date=''2013-02-04'']/ Balance)[1] +280')   where   [ID]=257 and [Date]='28-Feb-2013'  and  [dailybalance].exist('/Root/Row[date=''2013-02-04'']')=1;

Update account  set   [dailybalance].modify('replace value of   (/Root/Row[date=''2013-02-04'']/Transaction/text())[1] with   (/Root/Row[date=''2013-02-04'']/ Transaction)[1] +280')   where   [ID]=257 and [Date]='28-Feb-2013'  and  [dailybalance].exist('/Root/Row[date=''2013-02-04'']')=1;

The table has below structure

CREATE TABLE [dbo].[account ](
    [ID] [int] NULL,
    [Type] [char](10) NULL,
    [Date] [date] NULL,
    [Balance] [decimal](15, 2) NULL,
    [TRansaction] [decimal](15, 2) NULL,
    [mybal] [decimal](15, 2) NULL,
    [dailybalance] [xml] NULL,
    [AutoIndex] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_BalanceTable] PRIMARY KEY CLUSTERED
(
    [AutoIndex] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

I am using sql server 2008 r2 express.So i think i cannot use full text search. So please advice me how the index should be created so that above query performance is improved.

If you are updating the xml that often, then consider property promotion, ie converting the xml to relational tables, as also suggested by others.

Re the design, are you storing Balance and Transaction in more than one place?  This sounds dangerous.  If the xml is a kind of history table then it might be better as that.  You could also make sure the history table is populated at the same time as the main table within a transaction so it's safe.  Your current design doesn't feel safe to me.  The fact your two UPDATEs above aren't done in a transaction is a bit worrying.  Have you had mismatches between the main table and xml?

Having said all that, I did a simple test rig to try and reproduce this problem and the UPDATEs run in under a second for me on my laptop on a 1 million row table.  Can you see anything different between this rig and your queries?

USE tempdb
GO

IF OBJECT_ID('[dbo].[account]') IS NOT NULL DROP TABLE [dbo].[account]
GO
CREATE TABLE [dbo].[account](
    [ID] [int] NULL,
    [Type] [char](10) NULL,
    [Date] [date] NULL,
    [Balance] [decimal](15, 2) NULL,
    [TRansaction] [decimal](15, 2) NULL,
    [mybal] [decimal](15, 2) NULL,
    [dailybalance] [xml] NULL,
    [AutoIndex] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_BalanceTable] PRIMARY KEY CLUSTERED
(
    [AutoIndex] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

-- Generate some dummy rows
;WITH cte AS
(
SELECT TOP 1000000
    ROW_NUMBER() OVER( ORDER BY ( SELECT NULL ) ) AS rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.account ( ID, [TYPE], [Date], Balance, [Transaction], mybal, dailybalance )
SELECT
      rn AS ID
    , CHAR( 65 + ( (rn -1) % 6 ) ) AS [TYPE]
    , DATEADD( day, (rn -1) % 90, '1 Jan 2013' ) AS [Date]
    , RAND() * 10 * ( (rn -1) % 10 ) AS [Balance]
    , RAND() * 10 * ( (rn -1) % 10 ) AS [Transaction]
    , RAND() * 10 * ( (rn -1) % 10 ) AS [mybal]

    , ( SELECT CONVERT( CHAR(10), DATEADD( month, x.y, '1 Jan 2013' ), 120 ) AS "date", x.y AS "Balance", 1 AS "Transaction" FROM ( SELECT DISTINCT TOP 12 column_id y FROM master.sys.columns ) x FOR XML PATH('Row'), ROOT('Root'), TYPE ) [dailybalance]

FROM cte
GO

-- Create data for our condition
UPDATE a
SET [Date] = '28-Feb-2013',
    dailybalance.modify('insert
<Row>
  <date>2013-02-04</date>
  <Balance>-1</Balance>
  <Transaction>-1</Transaction>
</Row>

  as last into Root[1]
  ')
FROM dbo.account a
WHERE [ID] = 257
GO

-- Covering index for the query
-- CREATE INDEX _idx ON dbo.account ( ID, [Date] )

SELECT 'before' s, * FROM dbo.account
WHERE [ID] = 257

UPDATE account
SET [dailybalance].modify('replace value of (/Root/Row[date=''2013-02-04'']/Balance/text())[1]
with (/Root/Row[date=''2013-02-04'']/Balance)[1] +280')
WHERE [ID] = 257
  AND [Date] = '28-Feb-2013'
  AND [dailybalance].exist('/Root/Row[date=''2013-02-04'']') = 1;

UPDATE account
set [dailybalance].modify('replace value of (/Root/Row[date=''2013-02-04'']/Transaction/text())[1]
with (/Root/Row[date=''2013-02-04'']/Transaction)[1] +280')
where [ID] = 257
  and [Date] = '28-Feb-2013'
  and  [dailybalance].exist('/Root/Row[date=''2013-02-04'']') = 1;

SELECT 'after' s, * FROM dbo.account
WHERE [ID] = 257

How many rows in your account table?  If your xml is actually very large, then that might explain things.  Could there be other issues such as blocking, triggers, or is the server busy?  Looking at the query, it's unlikely full-text indexing would help you much.  XML Indexing might help, but at a significant storage penalty.  The table can end up between 2-5 its original size and you don't have that much room to play with in SQL Express 2008 R2 (10GB), so again probably not recommended.

One other thing that might help is a covering index to support the UPDATE, eg

CREATE INDEX _idx ON dbo.account ( ID, [Date] )

Try that and see if that helps your UPDATE.  If not, try and provide the additional information requested above and I'll have another look.


Fastest way to do bulk import in MYSQL I have got a question about the fastest way to copy a large number of records (around 10 million) from 1 table to another in MySQL.

The target table has 1 primary key, 4 indexes and 3 foreign key constraints. The source table has the same primary key, 3 equal indexes, no foreign keys.
Before copying I can truncate the target table, so it's not necessary to any duplicate key check (source table has the same primary key after all).

On my local development machine I have experimented with insert into, insert ignore into, insert into ... on duplicate key update, replace into.  The fastest result so far has been around 75 minutes (equal results for replace into and insert ignore into). Is there a faster way to do this?

(Seems like I'm able to post detailed answer here today... let me know if this has helped you)
Here is a test output for you...

mysql> truncate table t1;
Query OK, 0 rows affected (0.07 sec)

mysql> insert into t1 select * from t;
Query OK, 3842688 rows affected (36.19 sec)
Records: 3842688  Duplicates: 0  Warnings: 0

mysql>
mysql> \! rm /tmp/t.txt
mysql> select *  from t into outfile '/tmp/t.txt';
Query OK, 3842688 rows affected (7.24 sec)

mysql> load data infile '/tmp/t.txt' into table t1;
Query OK, 3842688 rows affected (25.20 sec)
Records: 3842688  Deleted: 0  Skipped: 0  Warnings: 0

With larger records and data-size you might see increased margin of difference.

If those are MyISAM tables then you might want to review following notes  from documentation:

  To ignore foreign key constraints during the load operation, issue a
  SET foreign_key_checks = 0 statement before executing LOAD DATA.

  If you use LOAD DATA INFILE on an empty MyISAM table, all nonunique
  indexes are created in a separate batch (as for REPAIR TABLE).
  Normally, this makes LOAD DATA INFILE much faster when you have many
  indexes. In some extreme cases, you can create the indexes even faster
  by turning them off with ALTER TABLE ... DISABLE KEYS before loading
  the file into the table and using ALTER TABLE ... ENABLE KEYS to
  re-create the indexes after loading the file.

Also loading into/from memory tables is much faster but based on size you've  mentioned I suspect we have sufficient memory (you might see table-full errors and issues due to that...)!

Anyways, try following commands and share the speed results:

select *  from t into outfile '/tmp/t.txt';

load data infile '/tmp/t.txt' into table t1;

(I guess <60 mins but no magic...)


Best way to document database mappings for migrations I'm working on a project that includes mapping database elements for a migration and I wanted to know what tools others were using to do this?

Excel is a very flexible way to document simple mappings but I was wondering if anybody had a specific methodology they followed or other tools they use that they could recommend?

The most common way to do this Excel, but it has it's tradeoffs:

Very difficult to update the spreadsheet if either side of the migration changes (due to missing something, something changing, etc.)
Excel is harder to use by the people implement the rules documented in it.
Change tracking is nearly impossible if you have multiple people collaborating on the document.

I prefer to use data lineage features of data modelling tools such as ER/Studio, ERwin or PowerDesigner to track the transformations required for migration from one data store to another.  I get all the benefits of RE and compares to the source and target, plus none of the tradeoffs mentioned above.  It really makes a difference.


Is it safe to "FLUSH STATUS" in mysql? We have deployed REDIS to hold some frequent query result to reduce number of connections made on Database server. We need to find out Max_used_connection after deploying REDIS. So we plan to reset mysql status variable Max_used_connection.
While googling i found to reset Max_used_connections we need to use FLUSH STATUS.
Is it safe to execute Flush status in production ?
MySQL docs says:

FLUSH STATUS -  resets the counters for key caches (default and named) to zero and sets Max_used_connections to the current number of open connections.

I don't know impact of resetting counters for key caches.
Is it possible to reset Max_used_connections alone ?
Thanks in Advance.

Is it safe to execute Flush status in production ?

Well it should not affect anything as such but resets the status counters and you should be "Safe".

  Is it possible to reset Max_used_connections alone ?

No

  but "We need to find out Max_used_connection after deploying ...."

How about introducing graphs into monitoring? Say Cacti / Grafana will give you good glances over what's-going-on-inside-your-mysql!


What does Oracle XE perform during installation? I have oracle11g2 installed on my noteobook.

I then installed the Oracle Database 11g XE. Now a .dll which uses the oracle11g2 client does not work anymore. First I recogniced that I had now two tnsnames.ora, which I resolved by linking the new one to the old one. But still my program cannot connect via the dll and throws an error.

I am relatively sure that the installation broke something which worked before so I need to know which steps the Oracle 11g XE installation procedure performs to system variable (like PATH) or to registry entries which might be important? Is there something like an installation log or can I somehow revert all changes which were done during installation?

My system is running Windows 7 x64.

Best regards, Fox

XE changes the following:

tnsnames.ora
Path variables
takes the default port 1521 for itself and this cannot be changed

The best way to install XE and other products is to install XE first, then any other Oracle version afterwards.

I think you can fix this by

examining the path variables by entering PATH at the command line
ensuring that each installation has a file path to the bin folder, something like this

c:\oracle\db11g\product\11.1.0\db_1\bin;
c:\XE\product\11.1.0\db_1\bin;
check that the listener.ora has the correct parameters by starting a command line and entering lsnrctl status  This will show you which listener is in use, it's location and what databases it is servicing.
change the port for the non XE database to anything other than 1521 in the listener.ora


SQL Server 2012 Crashes and service doesn't restart We've been having an issue with our SQL server lately. It seems that the server will run out of memory and then crash. For some reason, the sql service wouldn't restart. I would have to go and manually restart it and then it's fine.

I am still trying to figure out the memory issue. We are using SQL Server 2012 SP1 Enterprise Edition. This is on a Windows 2012 Server. I saw alot of 701 out of memory error on the server right before it crashed and I'm still trying to figure out what is causing the memory error.

Why wouldn't the SQL Service be restarted automatically? Is it because of the memory issue?

Also, server has 20gb of memory and the max server memory is set to 16.5gb. I have noticed that the MEMORYCLERK_SQLOPTIMIZER is really high, around 11.5 gb right before the server crashes.

Anyone know why that would be so high? Is that what's causing the memory errors?

Any help will be appreciated.
Thanks.

You are running into a known bug - There is a fix for it but update to SP2 rather than installing the hotfix.

The errorlog contains SQL Server is terminating a system or background task HADR Task due to errors in starting up the task (setup state 1). and you are using availability groups. The memory leak  is in the MEMORYCLERK_SQLLOGPOOL which is used for transaction log activity.


SQL Join slower than subquery on View with a Function My database has a view that calls a function. Joining on that view is surprisingly slow. Is there a way to speed up joining to a view like that?

I've included 2 queries which illustrate the issue. They give identical results. The first query takes 6 seconds to run. According to the estimated execution plan, the first query spends 82% of its time in "Nested Loops (Inner Join)". The second query runs much faster, but for more complicated queries I would have to use a join instead of a sub query.

I'm happy to provide more information about my particular system, but I suspect this is a generally applicable issue.

--ptynbr is int
--ptyid is varchar
--vwTest has 8307 rows
--map has 126 rows
--Instrument and Party are plain old tables.

Create function [dbo].[fnTest](@ptynbr int)
returns varchar(40)
as
begin
  declare @result varchar(40);
  select @result = ptyid from dbo.[Party] where ptynbr = @ptynbr;
  return (@result);
end;
GO

CREATE view [dbo].[vwTest] as
select dbo.[fnTest](i.ptynbr) PartyName, i.insaddr
from Instrument i
GO

select distinct i.PartyName
into #map
from [vwTest] i

--Takes 6 seconds
select i.insaddr
from [vwTest] i
join #map m on m.PartyName = i.PartyName

--Takes < 1 second.
 select i.insaddr
from [vwTest] i
where i.PartyName in (select PartyName from #map)

When you join on the field in the view which is generated by the function, SQL will need to scan the entire table and execute the function for every row in order to do the join, so this will inevitably be much slower than the equivalent set based operation.

You can improve matters a lot using a computed column.  The result of your function will be computed and cached by SQL each time dependent data is changed.


Storing information about office locations for international companies I have a table in MS Access containing a list of about 500 companies and some information about them. I am trying to create another table that contains information about the office locations of these companies. I am think to populate this table with the following fields:

Country, Company 1, Company 2.... all the way to Company 500.

The Country field will contain all the countries in the world. The other 500 fields will contain True/False values for whether the company has an office in the country in question.

Needless to say, this will be a huge table containing true/false values, furthermore it will be difficult to populate and enter data accurately for this table. Does anyone know of a more "correct" and data efficient way to store this information and query it easily? The objective is to be able to summon a list of all the countries where each company has an office location easily and efficiently. Or to start from a country and come up with a list of all companies present in that country.

To normalize this properly, you need what is sometimes called a cross-reference or link table, to avoid making that country table so wide.

For example (this is for demonstration purposes only):

company
-------
companyid
companyname

country
-------
countryid
countryname
countrycode

country_company
---------------
countryid
companyid

With this structure, when you add a new company, you add it to the company table, and then add a reference to it in the link table.

So if you had 100 countries, you would have several hundred rows in this table.

I can assure you that querying this will be far more efficient than a very wide table.

When you want to query the data, you'll need to write an additional JOIN, but this makes managing the data so much easier, and less error-prone.

Notice that I've added a countrycode column, which I would use for a two- or three-digit ISO standard (e.g. "USA", "CAN").

This means you could even use the code as a foreign key instead of the countryid.


SQL Server White Papers vs Books Online - What's the difference and where are the white papers? I've heard people reference white papers.  After doing a preliminary search for SQL-Server white papers, there are various sites with random pdfs.  I'm looking or white papers that will help me gain a deeper knowledge of SQL Server.

How are white papers different from books online?  Are white papers just a deeper dive into a technical issue?

Does Microsoft have an organized place for white papers? If so, where please?

Thank you for your help!

How are white papers different from books online? Are white papers
  just a deeper dive into a technical issue?

Books Online is the documentation, while white papers are often more technical discussions of a topic, some times with an lean toward marketing.

  Does Microsoft have an organized place for white papers? If so, where
  please?

Microsoft has an gallery of SQL white papers up through SQL Server 2014 here: https://social.technet.microsoft.com/wiki/contents/articles/13146.white-paper-gallery-for-sql-server.aspx

White papers for SQL 2017 and 2019 are here: https://info.microsoft.com/sql-server-2017-technical-white-paper-registration.html and https://info.microsoft.com/ww-landing-SQLDB-Microsoft-SQL-Server-WhitePaper.html

An SQL 2019 white paper focusing on big data is here: https://info.microsoft.com/ww-landing-SQL-Server-2019-Big-Data-WhitePaper.html

More can be found here: https://www.microsoft.com/en-us/search?q=white+paper+sql


Launch a stored procedure when a datetime is expired Can I launch in SQL Server a stored procedure when a datetime value (stored in a table) is expired?

I have a stored procedure that will send a message with MSMQ, and I need to do this as soon as the datetime is expired.

I am using SQL Server 2016.

You should create a SQL Agent job. This can poll your column and fire your stored procedure when the datetime exceeds a specified value.

Alternatively, if your stored procedure needs to run at exactly the specified time, schedule a SQL Agent job to fire at that exact time and don't poll. You can do this programmatically via the sp_add_jobschedule system stored procedure, or manually depending on your requirements.

Using the latter method you could remove the logic that writes the desired time to a table. If that table value is still required however, you could create a trigger on that table that invokes sp_add_jobschedule to create a job schedule.


plv8 fetch data from remote website I'm trying to retrieve data from openstreetmaps reverse geocoder with plv8. I have tried to look at the documentation but cannot find any info on how to get data from an URL which is returned as JSON to then parse it in the function.

Can anyone assist on this?

This sounds like a horrible idea. You're much better off

doing this in the app (where you can make better use of asyc with v8).
using PostGIS tiger geocoder

On the flip side, I don't think you can. PLv8 can load modules if they're in the database. But, I don't think it has network access.


Should I write a scalar function or a setof returning function for an INSERT/UPDATE/DELETE which can return at most one row? I want to embed certain INSERT, UPDATE or DELETE statements into a function, to enhance re-usability. These functions have in common that the are manipulating a single row (if it exists).

An example is the following function:

create function my_table_delete(_identifier int) returns setof int as
$BODY$
begin
    return query
    delete from    my_table
    where          identifier = _identifier
    returning      identifier;
end
$BODY$
language plpgsql volatile strict;

This function accepts an identifier, deletes the corresponding row and returns the same identifier in case the delete was successful.

I am quite unsure if I should implement this as a set-returning function (like I did above) or a scalar function.

My reasoning for preferring set-returning function is the following:

In case of a scalar function, we will have a NULL return value in case the row could not be found (while we will have 0 rows in case of the set-returning function). Having to deal with NULLs is a pain.
I'm working with sets (SQL is all about sets), so it makes sense to just keep doing that.
Also, I prefer returning the identifier (1 row: successful, 0 rows: not successful) instead of a boolean (true: successful, false: not successful), as it would allow constructs such as:

with d(id) as
(
    select * from my_table_delete(1234)
)
insert into log_table(id, action)
select      d.id, 'DELETE'
from        d;

If I were to return a boolean, the statement above could become less elegant.

I'm interested whether those arguments make sense. I'm quite new to the embedding logic into the database, so I'm not sure if I am addressing this correctly.

What do you think?

On a slightly related note, I also wonder if I should define the function as following:

create function my_table_delete(_identifier int)
    returns table (identifier int)
as ...

This gives the output row's column a name, which is even more convenient to use.

First, let's assert that manipulating a single row  is positively guaranteed by a UNIQUE or PRIMARY KEY constraint on my_table.identifier. Else it should be a table function to begin with.

Next: Should you write a function for such a simple DELETE at all? Or use the plain DELETE instead? Barely longer and less overhead. (Maybe your actual function is more sophisticated ...)

Then, while your function is that simple, a plain SQL function might serve:

CREATE FUNCTION my_table_delete(_identifier int)
  RETURNS TABLE(identifier int)
$func$
   DELETE FROM my_table t
   WHERE  t.identifier = _identifier
   RETURNING t.identifier;
$func$  LANGUAGE sql VOLATILE STRICT;

Also using RETURNS TABLE like you pondered: Note the table-qualified column name to avoid conflicts. All function parameters (including columns of a RETURNS TABLE clause) are visible in SQL commands in the function body (in PL/pgSQL as well as in SQL functions). You need to avoid conflicts between column names and parameters.

An SQL function has less overhead. But if you call the function many times in the same session, the PL/pgSQL variant may actually be faster since it manages SQL statements like prepared statements, meaning the query plan is saved and reused if some preconditions are met. Then again: much of the same effect is achieved with prepared statements. See:

Function Performance
Difference between language sql and language plpgsql in PostgreSQL functions
Select column values linked to exact set of values in another column

As to your reasoning:

1. If you don't want to deal with NULL, then the case is settled. Make it a table function. But you may have to deal with NULL after all: Various query constructs (have to) convert "no row" to NULL.

2. sounds like an argument, but is just a logical fallacy. Does not carry weight.

3. If it's your personal preference, then the case is settled again. Can't argue with that. But a data-modifying CTE would work with the scalar function just as well:

WITH d(id) AS (
   SELECT my_table_delete(1234)
   )
INSERT INTO log_table(id, action)
SELECT  d.id, 'DELETE'
FROM    d
WHERE   d.id IS NOT NULL;

In summary: go with RETURNS TABLE, but it hardly matters for the given case.


Finding all users with access to a table in SQL server Wondering if there is any script out there to extract a list of all database logins with access to a particular table, whether its given thru a view, database role, etc.

I think similar question has already been asked and you may refer the answers.

In the below query, you may pass your table name and this would work for you:

/*
Security Audit Report
1) List all access provisioned to a sql user or windows user/group directly
2) List all access provisioned to a sql user or windows user/group through a database or application role
3) List all access provisioned to the public role

Columns Returned:
UserName        : SQL or Windows/Active Directory user cccount.  This could also be an Active Directory group.
UserType        : Value will be either 'SQL User' or 'Windows User'.  This reflects the type of user defined for the
                  SQL Server user account.
DatabaseUserName: Name of the associated user as defined in the database user account.  The database user may not be the
                  same as the server user.
Role            : The role name.  This will be null if the associated permissions to the object are defined at directly
                  on the user account, otherwise this will be the name of the role that the user is a member of.
PermissionType  : Type of permissions the user/role has on an object. Examples could include CONNECT, EXECUTE, SELECT
                  DELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc.
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
PermissionState : Reflects the state of the permission type, examples could include GRANT, DENY, etc.
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
ObjectType      : Type of object the user/role is assigned permissions on.  Examples could include USER_TABLE,
                  SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc.
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
ObjectName      : Name of the object that the user/role is assigned permissions on.
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
ColumnName      : Name of the column of the object that the user/role is assigned permissions on. This value
                  is only populated if the object is a table, view or a table value function.
*/

--List all access provisioned to a sql user or windows user/group directly
SELECT
    [UserName] = CASE princ.[type]
                    WHEN 'S' THEN princ.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                 END,
    [UserType] = CASE princ.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                 END,
    [DatabaseUserName] = princ.[name],
    [Role] = null,
    [PermissionType] = perm.[permission_name],
    [PermissionState] = perm.[state_desc],
    [ObjectType] = obj.type_desc,--perm.[class_desc],
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM
    --database user
    sys.database_principals princ
LEFT JOIN
    --Login accounts
    sys.login_token ulogin on princ.[sid] = ulogin.[sid]
LEFT JOIN
    --Permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col ON col.[object_id] = perm.major_id
                    AND col.[column_id] = perm.[minor_id]
LEFT JOIN
    sys.objects obj ON perm.[major_id] = obj.[object_id]
WHERE
    princ.[type] in ('S','U')
UNION
--List all access provisioned to a sql user or windows user/group through a database or application role
SELECT
    [UserName] = CASE memberprinc.[type]
                    WHEN 'S' THEN memberprinc.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                 END,
    [UserType] = CASE memberprinc.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                 END,
    [DatabaseUserName] = memberprinc.[name],
    [Role] = roleprinc.[name],
    [PermissionType] = perm.[permission_name],
    [PermissionState] = perm.[state_desc],
    [ObjectType] = obj.type_desc,--perm.[class_desc],
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM
    --Role/member associations
    sys.database_role_members members
JOIN
    --Roles
    sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
JOIN
    --Role members (database users)
    sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
LEFT JOIN
    --Login accounts
    sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]
LEFT JOIN
    --Permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col on col.[object_id] = perm.major_id
                    AND col.[column_id] = perm.[minor_id]
LEFT JOIN
    sys.objects obj ON perm.[major_id] = obj.[object_id]
UNION
--List all access provisioned to the public role, which everyone gets by default
SELECT
    [UserName] = '{All Users}',
    [UserType] = '{All Users}',
    [DatabaseUserName] = '{All Users}',
    [Role] = roleprinc.[name],
    [PermissionType] = perm.[permission_name],
    [PermissionState] = perm.[state_desc],
    [ObjectType] = obj.type_desc,--perm.[class_desc],
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM
    --Roles
    sys.database_principals roleprinc
LEFT JOIN
    --Role permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col on col.[object_id] = perm.major_id
                    AND col.[column_id] = perm.[minor_id]
JOIN
    --All objects
    sys.objects obj ON obj.[object_id] = perm.[major_id]
WHERE
    --Only roles
    roleprinc.[type] = 'R' AND
    --Only public role
    roleprinc.[name] = 'public' AND
    --Only objects of ours, not the MS objects
    obj.is_ms_shipped = 0
ORDER BY
    princ.[Name],
    OBJECT_NAME(perm.major_id),
    col.[name],
    perm.[permission_name],
    perm.[state_desc],
    obj.type_desc--perm.[class_desc]

You may refer above from this question.

Alternatively you may use system defined views for the same:

select  princ.name
,       princ.type_desc
,       perm.permission_name
,       perm.state_desc
,       perm.class_desc
,       object_name(perm.major_id)
from    sys.database_principals princ
left join
        sys.database_permissions perm
on      perm.grantee_principal_id = princ.principal_id

This one is more similar to your requirement.

Hope above helps.


It's not documented in the official docs (as of now - Feb 2018), but PgPool's memory footprint is quite heavy.

In my recent testing PgPool II version 3.6 needs as much as 140 MB RAM per child process. (the number of child processes is defined in num_init_children). This is private process memory - not shared.

This means approximately 8GB for each 50 clients.

Compared to PostgreSQL, this is 5-10 times more (PostgreSQL can easily handle 250 sessions on 8 GB RAM). Plus, PostgreSQL uses shared buffer cache which is much more cost effective.


Are the contents of all control files the same? I understand that one needs to multiplex and back up the control file. Also one can have multiple control files in the database instance parameter setting. If one of these files get corrupted or is missing, the database will not be able to mount. My question is; the contents of all the control files, are they the same since one can simple do a copy of a good one, renaming it to that of a lost or corrupted one, and the database will startup?

yes, normally that is what you do, if you happen to know which file is the correct one. Make a copy before overwriting the file first.


Use of GROUP_CONCAT on Mysql As far as I understand, using or should be avoided as much as possible as rule of thumb in query.

I have two tables having one-to-many relationship.

And I have joined two tables like below.

SELECT A.*, group_concat(B.info separator ','), group_concat(B.info2 separator ',')
FROM A
JOIN B
ON A.id = B.a_id
GROUP BY A.id;

Does using GROUP_CONCAT affect the performance like or does in MySQL? If it does, what would be the efficient way to join those tables?

Any suggestion or advice would be appreciated. Thank you.

Your interests are generally not well-served by learning somewhat vague "rules" like "OR should be avoided" or "group_concat is slow."

Neither is actually correct, they only have elements of truth underlying them.

Use the correct capability to accomplish your purpose, while understanding that sometimes your interests are served by modifying your purpose to accommodate inherent limitations.

Using GROUP BY (not simply GROUP_CONCAT) can cause a performance hit if the optimizer has no effective way to accommodate the grouping... but this has nothing to do with whether GROUP BY should be used.  If you need it, then of course you should use it... but manipulating strings is a cake-walk compared to scanning disks and sorting without an index, and the CPU cycles consumed by the concatenation are near the noise floor.

The take-away here is to understand how the optimizer sees your query, and how it plans to resolve it.  Using EXPLAIN SELECT ... to verify that the server has made sensible choices (and that you have written a sensible query) should be second-nature to you if you want good performance.

https://dev.mysql.com/doc/refman/5.7/en/explain.html#explain-execution-plan

Interpreting the output can seem like a bit of a dark art, but you'll find it's worth the effort.


What is VPS\example.com_web identity in SQL server? I was facing connection failed for requested login in SQL server 2016 used for example.com domain on a VPS. I added the following logins to the database NT AUTHORITY\IUSR and NT AUTHORITY\NETWORK SERVICE and IIS AppPool\SampleAppPool but the login failed untill I added the VPS\EXAMPLE.COM_WEB (found from the advanced search) and login was successful.
What is the identity of EXAMPLE.COM_WEB? It is not a windows user, nor application pool nor ISUR and SERVICES. I have to mention that there is SolidCP installed on the server. I don't know if these kind of identity is related to the solidCP and why it is needed to connect to database?

This would be either a user generated and used in your application, or an AD group a user in your application is a part of, or a special AD group your application is reliant on while running in IIS. It has nothing directly to do with SQL Server. You may be able to find better information out on ServerFault.com or possibly StackOverflow.com.


How can I test sql scripts intended to run with sqlcmd -v in Sql Server Management Studio From msdn I learn, that the Variable Precedence of :Setvar X Y is higher, than that of sqlcmd -v X=Y.

I want to write a script, that uses the variables supplied to sqlcmd with the -v option, but which uses some default values, when run in Management Studio using SQLcmd mode.

Best there would be some way to supply the values to use somehow to the Management Studio without having to provide them in the script.

The precedence of setvar is the lowest.  Try it out and see if it works.


How to resolve error 15517 when DBO is set to SA? Newbie DBA here, just getting settled into my first DBA job and trying to figure out how things work outside of a classroom environment. Your patience is appreciated.

I'm trying to create diagrams for all the databases so I can try and get a grasp on how things are laid out here and a problem I'm running into is that when I attempt to create a diagram for one database in particular I'm getting the
 following error:

  Cannot execute as the database principal because the principal "dbo"
  does not exist, this type of principal cannot be impersonated, or you
  do not have permission. (Microsoft SQL Server, Error: 15517)

I've been searching for more info on this issue and it looks like it usually is caused by the owner of the database getting deleted but this database shows SA as the owner so that shouldn't be the issue. I'm not even able to create a diagram if I sign in as sa...

Not sure if this may have something to do with it but this particular database was migrated to a new server a few months ago so something may have broken when that was done (although the database is live and in use so it isn't entirely broken).

Maybe its looking for the sa account from the old server and it just needs to be updated to use the sa account on the current server?

Edit: This DB may not actually have an owner after all. Performing a Right click > Properties > Files on the database shows no owner for the database even though the owner field under general does.

I tried to change it through the GUI and was informed that set owner failed with the following error:

  Lock request time out period exceeded. (Microsoft SQL Server, Error:
  1222)

I also tried the alter authorization ... statement and it just never completes (or I cancelled it after a few minutes to be more accurate). Am I being to impatient with that statement or is something else going on here?

Try this to verify the owner of the database:

SELECT name AS DB, SUSER_SNAME(owner_sid) AS owner FROM sys.databases

If the owner is not sa, you can do ALTER AUTHORIZATION or use the deprecated command sp_changedbowner:

USE [*db*]
GO
EXEC sp_changedbowner 'sa'
GO

Even though it is deprecated, this procedure is still available in later versions and I have had more luck with that command for avoiding putting the database into single user mode.

Here is the sp_changedbowner doco


Execute a stored procedure via a Linked Server from SQL Server 2008 R2 to SQL Server 2014 We are in the planning stages for an upgrade from SQL Server 2008 R2 to SQL Server 2014.

Our system executes stored procedures against different databases on different servers.

If we upgrade one server to SQL Server 2014, but leave the remaining servers on SQL Server 2008 R2, would we lose that ability?

As a test, I just ran the following from my SQL Server 2008 R2 instance through a linked server to my SQL Server 2012 instance:

SELECT LocalServerName = @@SERVERNAME
    , LocalServerVersion = SERVERPROPERTY('ProductVersion');

EXEC [REMOTESERVER].master.sys.sp_executesql
    N'SELECT RemoteServerName = @@SERVERNAME
        , RemoteServerVersion = SERVERPROPERTY(''ProductVersion'');';

This will show two result-sets, one for the local server, with server name, and version, and one for the remote server with the same details.

Executing this will prove that you can run stored procedures from down-level servers to up-level servers.


Same Query takes 0 seconds on Server A, 7.5 minutes on Server B (same db/hardware/config) I have a query (sql below) that is nearly instantaneous on 1 server, but takes 7.5 minutes on another virtually (key point) identical server.  The estimated query execution plans have one obvious difference;  the plan on the slower performing server contains an "Index Spool (Eager Spool)" where the plan on the faster performing server does not.

Server F (fast) - A snap of Server S that is refreshed monthly.  It is used by only a handful of users for intensive analysis and has been separated so day to day users don't have to suffer the performance hits.

Server S (slow) - Receives daily data updates and is generally used for ad-hoc data mining and a small amount of user workflow data updates.  There is no user activity or process running when this sql takes 7.5 minutes to run.

The hardware and configurations are identical.  One difference is that the indexes on Server B are only rebuilt/reorganized once per week.  They are rebuilt on Server F just after restoring the snap.  I don't think this is a problem though because on Server S all index fragmentation on the large IB Charge table is < 5%.

The sql may be complex or ugly, but it runs quickly on 1 server.  The index fragmentation does not appear to be much higher on the slower server than the fragmentation on the slower server.

What is causing such a drastic difference?  I'm struggling with deciding what to investigate next.

select
row_number() over (order by t1.service_date) as rank,
sum(t1.total_charge) total_charge,
sum(t1.service_units) service_units,
t1.service_date service_date,
(   select sum(x.total_charge)
    from ib_charge x
            inner join ib_header y on x.ib_header_fk = y.ib_header_pk
    where x.patient_account_fk = ###
            and x.record_type = 1
            and x.voided = 0
            and y.replaced = 0
            and x.revenue_code not in (274, 275, 276, 278)
        and (x.service_date is null or x.service_date <= t1.service_date)
    ) running_total_by_day
from
ib_charge t1
inner join ib_header t2 on t1.ib_header_fk = t2.ib_header_pk
where
t1.patient_account_fk = ###
and t1.record_type = 1
and t1.voided = 0
and t2.replaced = 0
and t1.revenue_code not in (274, 275, 276, 278)
group by t1.service_date
order by t1.service_date

Execution Plan Server F
Server_F.sqlplan

Execution Plan Server S
Server_S.sqlplan

SERVER F statistics io

(55 row(s) affected)

Table 'IB_Header'. Scan count 0, logical reads 60611, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'IB_Charge'. Scan count 56, logical reads 121156, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SERVER S statistics io

(55 row(s) affected)

Table 'IB_Header'. Scan count 0, logical reads 60706, physical reads 0, read-ahead reads 2, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 55, logical reads 384302241, physical reads 12, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'IB_Charge'. Scan count 2, logical reads 1686771, physical reads 4143, read-ahead reads 1684432, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    UPDATE >> I also put this question out as an office challenge, and on Monday someone congratulated me on having solved the problem - but I hadn't done anything!  A weekly statistic refresh and index rebuild had run over the weekend, but the particular index stats on the tables involved don't look much different.  On further investigation, I realized that depending on which particular KEY is used in the ### portion of the query the results will take either 0 seconds or 5-8 minutes.  Just editing the KEY also causes the estimated execution plan to include/exclude the index spool which seems to be the primary delay in the query.  Currently pursuing a more holistic investigation of the bpool.

This problem has been resolved, although not quite how I anticipated.

Performance did not change after flushing the buffer pool, the procedure cache, rebooting the server, or rebuilding the indexes.  The lengthy index spool continued to appear sometimes and not others in the query plan.

The fix was to change the reference in the where clause from ib_charge.patient_account_fk = ### to ib_header.patient_account_fk  (the itemized bill (IB) header table is much smaller than the ib_charge table).  This resulted in the server ceasing to use the Index Spool in all cases, which was the cause of the performance hit.


Oracle 12c: installing expdp and impdp on Linux The default Oracle installation for Linux didn't come with the expdp and impdp command line tools. I have trouble finding them on Oracle's website. How could I install them?

Data Pump utilities are installed automatically during the installation of Oracle Database. It should be working without any issues as long as the environment variables(PATH, ORACLE_HOME) are set properly. To set PATH and ORACLE_HOME variable permanently-

1) Log in as oracle user in the OS.

2) [oracle@ora12c Desktop]$ vi ~/.bash_profile

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/12.1.0/db_1; export ORACLE_HOME
PATH=.:${JAVA_HOME}/bin:${PATH}:$HOME/bin:$ORACLE_HOME/bin

3)[oracle@ora12c Desktop]$ . ~/.bash_profile

4)Check the value of the variables.

[oracle@ora12c Desktop]$ echo $ORACLE_HOME
/u01/app/oracle/product/12.1.0/db_1
[oracle@ora12c ~]$ echo $PATH
.:/usr/local/java/bin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/usr/bin:/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin:/u01/app/oracle/product/12.1.0/db_1/bin:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin:/u01/app/common/oracle/bin

5) Try to run expdp

[oracle@ora12c Desktop]$ expdp

Export: Release 12.1.0.2.0 - Production on Tue Oct 4 16:46:44 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Username:

Setting Oracle Environment Variables


Why can I select all fields when grouping by primary key but not when grouping by another column How is this a valid statement (where id is the primary key of the table):

select * from table group by id ;

and this is not:

select * from table group by name ;

  ERROR:  column "pgluser.id" must appear in the GROUP BY clause or be used in an aggregate function

Fiddle.

The question is why is the first a legal query, ie why grouping by primary key is valid?

id is a primary key.
As far as I remember, this is actually a legal query according to ANSI/ISO SQL.
      Grouping by primary key results in a single record in each group which is logically the same as not grouping at all / grouping by all columns, therefore we can select all other columns.

create table t (id int primary key,c1 int,c2 int)
insert into t (id,c1,c2) values (1,2,3),(4,5,6);
select * from t group by id;

+----+----+----+
| id | c1 | c2 |
+----+----+----+
| 1  | 2  | 3  |
+----+----+----+
| 4  | 5  | 6  |
+----+----+----+

Reference given by @a_horse_with_no_name

https://www.postgresql.org/docs/current/static/sql-select.html#SQL-GROUPBY

  When GROUP BY is present, or any aggregate functions are present, it
  is not valid for the SELECT list expressions to refer to ungrouped
  columns except within aggregate functions or when the ungrouped column
  is functionally dependent on the grouped columns, since there would
  otherwise be more than one possible value to return for an ungrouped
  column. A functional dependency exists if the grouped columns (or a
  subset thereof) are the primary key of the table containing the
  ungrouped column.

While logically we would expect UNIQUE NOT NULL to follow the same behaviour, it applies only for PK (as described in the documentation)

create table t (id int unique not null,c1 int,c2 int);
insert into t (id,c1,c2) values (1,2,3),(4,5,6);
select * from t group by id;

  [Code: 0, SQL State: 42803]  ERROR: column "t.c1" must appear in the
  GROUP BY clause or be used in an aggregate function


Query two SQL Server databases on two different servers Suppose I have two database servers (DB-SERVER-1 & DB-SERVER-2), and there is a single database on each server (DB1 & DB2).  Both are SQL Server databases.

Is it possible to query both databases in the same T-SQL command or function?  Perhaps by connecting to one, then getting a result set, then connecting to the other, and getting that result set.  Then performing a union with both result sets?

I can do this from within .NET or Java code, but I want to do this strictly from the database side.  Is this even possible?

You can indeed do this, but I recommend against it.  You can create a linked server on DB-SERVER-1, connecting it to DB-SERVER-2.

(A linked server definition is setup by the DBA.  It contains the login credentials to the remote server.  If you want to provide the login credentials yourself each time, that would be an openquery or openrowset command.)

You then could write a query using four part naming convention as follows, assuming you are running this query on DB-SERVER-1:

select *
  from DB1.dbo.YourTable a
  join [DB-SERVER-2].DB2.dbo.OtherTable b on b.ID = a.ID

Cross server joins are notorious for having performance problems.  I would only do it for administrative purposes, and definitely not against large result sets.


16 Cores 12 GB RAM server MySql Configuration - my.cnf The server takes 20+ seconds (wait time/slow IO response time) to response to a HTTP request even with memcached and APC installed. I believe this has something to do with MYSQL since the site as lots INSERT queries.

Any help would be greatly appreciated. Thanks in advance.

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
               6.38    0.03    1.05    0.40    0.00   92.14
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          10.37    0.00    1.61    3.14    0.00   84.87
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           9.40    0.00    1.41    1.53    0.00   87.67
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          10.02    0.00    1.42    1.09    0.00   87.46
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           9.32    0.00    1.31    0.78    0.00   88.59
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          10.62    0.00    1.57    2.37    0.00   85.44

Dedicated server specs:

2 x Intel Xeon-Westmere 5620-Quadcore [2.4GHz] processors
6 x Hynix DDR3 2GB DDR3 x8 [2GB] (total of 12GB)
2 x Western Digital WD Caviar RE4 WD5003ABYX [500GB]
MySQL version: 5.1.56-log MySQL Community Server (GPL)
Current database size: 4.2 GiB ~25,350,659rows

Here is the current my.cnf configuration:

[mysqld]
set-variable = max_connections=1024
log-slow-queries
safe-show-database

back_log                        = 80
max_connect_errors              = 9999999
table_cache                     = 5000
binlog_cache_size               = 1M
max_heap_table_size             = 128M
sort_buffer_size                = 500K
join_buffer_size                = 500K
thread_cache_size               = 100
#thread_concurrency              = 16
query_cache_size                = 512M
query_cache_limit               = 8M
query_cache_min_res_unit        = 2K
thread_stack                    = 192K
tmp_table_size                  = 384M
default_table_type              = INNODB

long_query_time                 = 2
log-slow-queries=/var/log/mysql.slow.queries.log

innodb_additional_mem_pool_size = 80M
innodb_buffer_pool_size         = 9G
innodb_file_io_threads          = 4
innodb_thread_concurrency       = 33
innodb_log_buffer_size          = 20M
innodb_log_file_size            = 800M
innodb_log_files_in_group       = 3
innodb_max_dirty_pages_pct      = 90
innodb_lock_wait_timeout        = 120
innodb_file_per_table           = 1

read_rnd_buffer_size=2M

thread_concurrency=33

@DTest
Table Structure

CREATE TABLE IF NOT EXISTS `nameapp` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `uid` bigint(20) NOT NULL,
  `name` varchar(225) CHARACTER SET latin1 NOT NULL,
  `pub` int(11) NOT NULL,
  `datetime` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;

Slow query log

# User@Host: exootnet_lab[exootnet_lab] @ localhost []
# Query_time: 2.044988  Lock_time: 0.000098 Rows_sent: 0  Rows_examined: 0
SET timestamp=1326290464;
INSERT INTO `nameapp` (`uid`,`name`,`pub`,`datetime`) VALUES ('455330','AWESOMENESS','1','1326290462');

As a first course of action, I'd watch the output of 'iostat -xk 10 10' to see if the disk IO is saturating the line.

I notice that you sent specs for your memory system, but not your disk system. What's the underlying array here that you're writing so much to? If you are insert-heavy, then that could play a big part.


Why does UPDLOCK cause SELECTs to hang (lock)? I've got a select in SQL SERVER that locks the whole table.

Here's the setup script (make sure you don't overwrite anything)

USE [master]
GO

IF EXISTS(SELECT 1 FROM sys.databases d WHERE d.name = 'LockingTestDB')
DROP DATABASE LockingTestDB
GO

CREATE DATABASE LockingTestDB
GO

USE [LockingTestDB]
GO
IF EXISTS(SELECT 1 FROM sys.tables t WHERE t.name = 'LockingTestTable')
  DROP TABLE LockingTestTable
GO

CREATE TABLE LockingTestTable (
  Id int IDENTITY(1, 1),
  Name varchar(100),
  PRIMARY KEY CLUSTERED (Id)
)
GO

INSERT INTO LockingTestTable(Name) VALUES ('1')
INSERT INTO LockingTestTable(Name) VALUES ('2')
GO

Open a new query window and run the following transaction (which has a wait in it):

USE [LockingTestDB]
GO

BEGIN TRANSACTION
  SELECT * FROM LockingTestTable t WITH (UPDLOCK, ROWLOCK) WHERE t.Name = '1'
  WAITFOR DELAY '00:01:00'

COMMIT TRANSACTION
--ROLLBACK
GO

USE [master]
GO

And another one that will run (make sure they run at the same time):

USE [LockingTestDB]
GO

SELECT * FROM LockingTestTable t WITH (UPDLOCK, ROWLOCK) WHERE t.Name = '2'

USE [master]
GO

You'll notice the second query will be blocked by the first one. Stop the first query and execute the ROLLBACK and the second will complete.

Why is this happening?

PS: Adding a non-clustered index (with full coverage) over Name will fix it:

USE [LockingTestDB]
GO

CREATE NONCLUSTERED INDEX [IX_Name] ON [dbo].[LockingTestTable]
(
  [Name] ASC
)
INCLUDE ( [Id]) WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Again why?

As documented in Books Online, UPDLOCK takes update locks and holds them to the end of the transaction.

Without an index to locate the row(s) to be locked, all tested rows are locked, and locks on qualifying rows are held until the transaction completes.

The first transaction holds an update lock on the row where name = 1. The second transaction is blocked when it attempts to acquire an update lock on the same row (to test if name = 2 for that row).

With an index, SQL Server can quickly locate and lock only those rows that qualify, so there is no conflict.

You should review the code with a qualified database professional to validate the reason for the locking hint, and to ensure appropriate indexes are present.

Related information: Data Modifications under Read Committed Snapshot Isolation


How to setup mysql on tmpfs? How to setup mysql on tmpfs ?

I am reading a tutorial in french, where the author do it this way :

1.) Create a tmpfs directory :

mkdir /var/tmpfs
mount -t tmpfs -o size=1G tmpfs /var/tmpfs
chown -R mysql:mysql /var/tmpfs/mysql

2.) Add it to fstab :

tmpfs  /var/tmpfs  tmpfs  nodev,nosuid,noexec,noatime,size=1G  0 0

3.) Copy MySQL to tmpfs (is ramfs here is correct?)

service mysql stop
cp -Rfv /var/lib/mysql /var/ramfs/
chown -R mysql:mysql /var/tmpfs/mysql

4.) Edit /etc/my.cnf

datadir /var/tmpfs/mysql

Then restart mysqld and you can optionally add a startup/stop script to backup the ram data to disk...

But I am not sure the above is right : did the writer of this tutorial mistake ramfs for tmpfs here ? He then goes on to use ramfs in the start/stop script, but what ramfs has to do there, isn't it really tmpfs only ?

Seems like it was really

cp -Rfv /var/lib/mysql /var/tmpfs/

Working great now.