texts:
I am using InnoDB tables in my database and total index_size is 250 GB and my data size is 500 GB. Server has 128 GB RAM, and innodb_buffer_pool_size is set to 64 GB. Data size is increasing every day and select queries performance is degrading because of swaps. What can be done to improve select query performance?

show variables like 'innodb%' output :

innodb_adaptive_flushing        ON
innodb_adaptive_hash_index      ON
innodb_additional_mem_pool_size 4194304
innodb_autoextend_increment     8
innodb_autoinc_lock_mode        0
innodb_buffer_pool_instances    1
innodb_buffer_pool_size 68719476736
innodb_change_buffering all
innodb_checksums        ON
innodb_commit_concurrency       16
innodb_concurrency_tickets      500
innodb_data_file_path   ibdata1:10M:autoextend
innodb_data_home_dir
innodb_doublewrite      ON
innodb_fast_shutdown    1
innodb_file_format      Antelope
innodb_file_format_check        ON
innodb_file_format_max  Antelope
innodb_file_per_table   ON
innodb_flush_log_at_trx_commit  1
innodb_flush_method     O_DSYNC
innodb_force_load_corrupted     OFF
innodb_force_recovery   0
innodb_io_capacity      200
innodb_large_prefix     OFF
innodb_lock_wait_timeout        60
innodb_locks_unsafe_for_binlog  OFF
innodb_log_buffer_size  67108864
innodb_log_file_size    805306368
innodb_log_files_in_group       4
innodb_log_group_home_dir       ./
innodb_max_dirty_pages_pct      75
innodb_max_purge_lag    0
innodb_mirrored_log_groups      1
innodb_old_blocks_pct   37
innodb_old_blocks_time  0
innodb_open_files       300
innodb_print_all_deadlocks      OFF
innodb_purge_batch_size 20
innodb_purge_threads    0

innodb_random_read_ahead        OFF
innodb_read_ahead_threshold     56
innodb_read_io_threads  4
innodb_replication_delay        0
innodb_rollback_on_timeout      OFF
innodb_rollback_segments        128
innodb_spin_wait_delay  6
innodb_stats_method     nulls_equal
innodb_stats_on_metadata        ON
innodb_stats_sample_pages       8
innodb_strict_mode      OFF
innodb_support_xa       ON
innodb_sync_spin_loops  30
innodb_table_locks      OFF
innodb_thread_concurrency       128
innodb_thread_sleep_delay       10000
innodb_use_native_aio   OFF
innodb_use_sys_malloc   ON
innodb_version  5.5.36

innodb_write_io_threads 4

Edit:

This server is a backup server and is mostly read only. Read frequency is also very low. But the queries are such that it wants to access a months data from a table.

mysql> show table status like 'TABLE_NAME'\G

Name: TABLE_NAME
Rows: 456194631
Avg_row_length: 60
Data_length: 27472723968
Index_length: 14432813056
Data_free: 14381219840

This table has data of 6 months and query is like :

select col1 from TABLE_NAME where col2 = 4 and Time_1 BETWEEN "2014-12-01" and "2014-12-30".

BTREE index is created on both col2 and Time_1.

explain query Output :

possible_keys = Time_1,Col2
key used = Col2
key_len = 3
ref = const
rows = 98126
Extra = Using where

But this query takes more than 20 minutes to execute. What can be done to improve the performance?


texts: Is There A Benefit To Using SSIS Packages Over Linked Servers?

texts:
We have a new head DBA, and he has decided he does not want us to use linked servers for development moving forward. In addition he would like to replace all existing code that uses linked servers to use SSIS packages instead. This presents a difficulty for the development staff, because rewriting all existing code to use SSIS packages represents a substantial time commitment, hundreds of hours of development and testing due to the volume of code already in place.

So my questions are this:

1) Have linked servers really fallen out of favor in the development community?

2) Do they represent a security risk?

3) Are there performance considerations that make SSIS packages superior to linked servers?

4) If linked servers are problematic, why has the code that uses them been functioning without issue for the last eight years?

5) Are there any functional benefits for using SSIS packages over linked servers that would justify such a massive development effort?

For context, the majority of code the uses linked servers is moving data between in house and vendor databases. For example, in one case it inserts all records that exist in a table in the custom database that don't yet exist in a corresponding table in the vendor database. In another it updates columns in the custom database based on the values in the vendor database. There's just a substantial amount of code spread out between a number of different databases that uses linked servers.


texts: Averaging 2 values based on an ID in SSRS

texts:
I am hoping I might be able to get some help with a problem I am having. I am currently trying to build a report in SSRS to replace an excel spreadsheet we use for data analysis so the report can be dynamic. I have set the report to be a matrix and pull through all the reported grades for a student. I also have a column that shows their Most Likely Grade (MLG). My problem is that students have more than one teacher for each subject, so what I need to do is average out the two attainment grades and then compare it to the average MLG. The MLG has a transposed value stored in the database that I use in calculation, so for example an A actually = 1.

In essence, in the example pictured - History MLG = A,A = (1+1)/2 = MLG of 1
Attainment score for Spring is (1+2)/2 = 1.5, so the value returned to the column would be 1.5.

My logic would be to look for the subject ID, if the subject ID is the same as another in the column,then add the 2 attainment scores, average them and return the average value into a new column.

I am very new to SSRS so I am not sure if this is possible and if it is how to get this to work.

This is the SQL query on the dataset

WITH Pupils AS
(SELECT DISTINCT txtSchoolID
FROM TblReportsStorePupilArchive
WHERE (TblReportsStorePupilArchive.intReportCycle = @intReportCycle)
AND (TblReportsStorePupilArchive.intNCYear IN
(SELECT ID FROM dbo.SplitCSV(@intNCYear) AS SplitCSV_1) OR @intNCYear IS NULL)
AND (TblReportsStorePupilArchive.txtForm IN
(SELECT ID FROM dbo.SplitCSV(@txtForm) AS SplitCSV_1) OR @txtForm IS NULL)
AND (TblReportsStorePupilArchive.txtSchoolID IN
(SELECT ID FROM dbo.SplitCSV(@txtSchoolID) AS SplitCSV_1) OR @txtSchoolID IS NULL)
AND (TblReportsStorePupilArchive.txtBoardingHouse IN
(SELECT ID  FROM dbo.SplitCSV(@txtHouse) AS SplitCSV_1) OR @txtHouse IS NULL
OR
TblReportsStorePupilArchive.txtAcademicHouse IN
(SELECT ID FROM dbo.SplitCSV(@txtHouse) AS SplitCSV_1) OR @txtHouse IS NULL)
),

Cycles AS
(SELECT TblReportsManagementCycleID FROM  TblReportsManagementCycle AS AcademicYearCycles
    WHERE AcademicYearCycles.intReportYear =
            (SELECT intReportYear FROM TblReportsManagementCycle AS CurrentCycle
            WHERE CurrentCycle.TblReportsManagementCycleID = @intReportCycle)
)

SELECT AcademicReports.*

FROM VwReportsManagementAcademicReports AS AcademicReports

INNER JOIN Pupils ON AcademicReports.txtSchoolID = Pupils.txtSchoolID
INNER JOIN Cycles ON AcademicReports.intReportCycleID = Cycles.TblReportsManagementCycleID


texts: How to generate globally unique ids for different tables of the same database?

texts: In a production system on SQL Server all ids (mostly PKs) in all tables are generated automatically and I am informed that they are unique globally. I mean no 2 ids are the same in the database, even if tables are different. I want to know how this can be done? If there are multiple ways please list them all. Thanks.


texts: a filtered index to support a query - is it possible to consider nulls?

texts:
I have been dealing with this query:

SELECT @amount = sum(accountAmount)
FROM dbo.accountTransaction
WHERE invoiceID = @invoiceID
 AND (notCountedInTotal = 0 OR notCountedInTotal IS NULL)

ans as I have had good experiences with filtered indexes

I can create this one:

 CREATE NONCLUSTERED INDEX F_INVOICEID  ON dbo.accountTransaction (  INVOICEID  )  INCLUDE ( accountAmount)
 where (notCountedInTotal is null)

But that would not solve my query problem above.
So I would like to have the following filtered index:

  CREATE NONCLUSTERED INDEX F_INVOICEID  ON dbo.accountTransaction (  INVOICEID  )  INCLUDE ( accountAmount)
 WHERE (notCountedInTotal IN (null,0))

  Msg 10620, Level 16, State 1, Line 38 Filtered index 'F_INVOICEID'
  cannot be created on table 'dbo.accountTransaction' because the filter
  expression contains a comparison with a literal NULL value. Rewrite
  the comparison to use the IS [NOT] NULL comparison operator to test
  for NULL values.

is it at all possible?

would it be worth creating a computed column, persisted, that would put the zeros and nulls together and create the index on that?

is there a work around for when you want to put an OR inside a filtered index?

would I not get into the same issue as below?

Unable to create a Filtered Index on a Computed Column


texts: Delete rows from a large table with joins and aggregating (resulting in 1 % less rows in the big table)

texts:
I got a bit stuck on performance, so I thought about asking a bit of help. I have a working query, but unfortunatelly it feels rather slow and I know it's not as performant as it could be made (easily?). The situation is to delete rows from a table which contain around one million rows, by doing a join to another table likewise having around a million rows worth of data (these tables have about the same amount of rows). The result after removing rows from SomeList is that there's about 1 % less rows

The table structure is as follows

CREATE TABLE SomeList
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    TimeData DateTime NOT NULL
);

CREATE TABLE SomeListAuxData
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    CountData INT NOT NULL,
    SomeListId INT FOREIGN KEY REFERENCES SomeList(Id) ON DELETE CASCADE
);

And the query to delete as follows

DELETE
FROM SomeList
WHERE TimeData < @someTime AND Id = @someListId AND
(
    SELECT COUNT(*)
    FROM SomeListAuxData
    WHERE SomeList.Id = SomeListAuxData.SomeListId
) > 0
AND
(
    SELECT MAX(CountData)
    FROM SomeListAuxData
    WHERE SomeList.Id = SomeListAuxData.SomeListId
) < @someValue;

Specifically this seem wasteful since I'm doing two subqueries, but I'm not sure how to go about the COUNT(*) and MAX(CountData) parts if I'd try joining, for instance.

This query is made in a program code where the someListIds are looped with same the @someValue (e.g. 2000) and @someTime (e.g. '2013-11-07 09:00:00.000') dates, so that too is rather slow. The query could be done for all the SomeList rows at once.

<edit: Also, I just learned that the code calling does have a list of pairs of type (@someListId, @someDate), which means the date isn't a constant. The input comes from the user interfaces and typically there may be even hundreds of such pairs.

Also, maybe scheduling a job at night may do the job. Though I'll need to checks Craig's advice which seem to be valid too. :)

<edit: I clarified what I meant by the "1 %" in my question. The point to make was that I don't think the data warrants collecting the non-removable rows to a #temptable, truncating SomeList and then moving the data from #temptable" back to SomeList. Craig's good point answered (partially) a different question, nevertheless it may be applicable. Sorry for my sloppy writing.

<edit 2013-11-08: The join conditions corrected as suggeted in comments.


texts: How do I copy a DB dump file to a remote computer?

texts:
I want to create a copy of my database from one server to another (my laptop to a server for example). Ive been looking online about how to do it and found this article:http://www.postgresqltutorial.com/postgresql-copy-database/ However I am confused when it says

  "Second, copy the dump file to the remote server"

I know how to pg_dump and pg_restore but now sure what they mean by copy the dump file?


texts: Is a MERGE with OUTPUT better practice than a conditional INSERT and SELECT?

texts:
We often encounter the "If not exists, insert" situation. Dan Guzman's blog has an excellent investigation of how to make this process threadsafe.

I have a basic table that simply catalogs a string to an integer from a SEQUENCE. In a stored procedure I need to either get the integer key for the value if it exists, or INSERT it and then get the resulting value. There is a uniqueness constraint on the dbo.NameLookup.ItemName column so data integrity isn't at risk but I don't want to encounter the exceptions.

It's not an IDENTITY so I can't get SCOPE_IDENTITY and the value could be NULL in certain cases.

In my situation I only have to deal with INSERT safety on the table so I'm trying to decide if it's better practice to use MERGE like this:

SET NOCOUNT, XACT_ABORT ON;

DECLARE @vValueId INT
DECLARE @inserted AS TABLE (Id INT NOT NULL)

MERGE
    dbo.NameLookup WITH (HOLDLOCK) AS f
USING
    (SELECT @vName AS val WHERE @vName IS NOT NULL AND LEN(@vName) > 0) AS new_item
        ON f.ItemName= new_item.val
WHEN MATCHED THEN
    UPDATE SET @vValueId = f.Id
WHEN NOT MATCHED BY TARGET THEN
    INSERT
      (ItemName)
    VALUES
      (@vName)
OUTPUT inserted.Id AS Id INTO @inserted;
SELECT @vValueId = s.Id FROM @inserted AS s

I could do this witout using MERGE with just a conditional INSERT followed by a SELECT
I think that this second approach is clearer to the reader, but I'm not convinced it's "better" practice

SET NOCOUNT, XACT_ABORT ON;

INSERT INTO
    dbo.NameLookup (ItemName)
SELECT
    @vName
WHERE
    NOT EXISTS (SELECT * FROM dbo.NameLookup AS t WHERE @vName IS NOT NULL AND LEN(@vName) > 0 AND t.ItemName = @vName)

DECLARE @vValueId int;
SELECT @vValueId = i.Id FROM dbo.NameLookup AS i WHERE i.ItemName = @vName

Or perhaps there is another better way that I haven't considered

I did search and reference other questions. This one: https://stackoverflow.com/questions/5288283/sql-server-insert-if-not-exists-best-practice is the most appropriate I could find but doesn't seem very applicable to my use case. Other questions to the IF NOT EXISTS() THEN approach which I don't think is acceptable.


texts: Is it always bad to have an own table for value objects?

texts:
I know that value objects should be embedded in the table of the object they belong to but what if the value object is more than a simple object and contains many fields/columns or even sub-objects?

I currently have a table named content which in short stores rich text. Since I don't always parse the content I've added a caching system. The table also has a 1:n-relation to another table which contains a list of all the links in the content table.

content(content_id[PK], text)
content_link(link_id[PK], content_id[FK], object_id)

If I would put this data in the orginal table I would follow the rules of database design but many it would make work harder.


texts:
If I give a user the rights to read on my secondary replica which is in sync (not async) with primary replica, and if that user runs a query with a lot of Select into ##Temptable statements on the database (that creates a lock on a table), will that slow down the Primary Replica?
I was thinking if the deadlock and locks on secondary replica will cause the slowness on the Primary replica, since they are synchronous and the primary should wait for the secondary to commit the transaction?


texts:
I can't connect to server. Using psql -h 127.0.0.1 -U postgres -d mydb and pgAdmin, in both case:

psql: could not connect to server: Connection refused
    Is the server running on host "127.0.0.1" and accepting
    TCP/IP connections on port 5432?

I changed the pg_hba.conf and postgresql.conf

listen_addresses = '*'

host    all             postgres        0.0.0.0/0               md5

When I restart the service:

2019-05-03 18:04:01 -03 [23773-1] LOG:  peer authentication is only supported on local sockets
2019-05-03 18:04:01 -03 [23773-2] CONTEXT:  line 92 of configuration file "/etc/postgresql/9.5/main/pg_hba.conf"
2019-05-03 18:04:01 -03 [23773-3] FATAL:  could not load pg_hba.conf

Update

I already change md5 to peer.

92  host    all     all         127.0.0.1/32    peer
93  host    all     postgres    0.0.0.0/0       md5

ps -ef | grep post

thiago  3583 21771  0 09:04 pts/2    00:00:00 grep --color=auto post
root     24726  8345  0 Mai03 pts/1    00:00:00 sudo -i -u postgres
postgres 24727 24726  0 Mai03 pts/1    00:00:00 -bash


texts: Error with restoring from a split .bak file with password

texts:
I am trying to restore a backup from a split .bak file[.bak_1/.bak_2/.bak_3]. The backup is password protected. So far this is what I have and I cannot seem to format in a way that does not throw me an error.

USE master
GO
RESTORE DATABASE client_a_database1
  FROM DISK = 'G:\database1\***.bak_1',
       DISK = 'G:\database1\***.bak_2',
       DISK = 'G:\database1\***.bak_3'
  WITH MOVE 'database1_data' TO 'G:\database1\***.mdf'
       MOVE 'database1_log' TO 'G:\database1\***.ldf', REPLACE,
  Password 'password'
  GO

With this format I am getting an error on the Password that I have added.


texts: How to grant access permision for multiple database and multiple tables to a user in MySQL?

texts: I have two databases WORLD,TEST each one contain 3 Tables.I also created a new User test_grant now i want to give SELECT,DELETE,UPDATE permission to test_grant user.But here i want to give two tables permission of TEST database rather than all tables permission in single SQL statement.


texts:
Is it possible to modify the ('%') to return a single row for the following query, i.e. while keeping the entire statement before it?

select 1 FROM DUAL WHERE TRIM('asdfsdf') in ('%')


texts:
My tables may or may not have the same employee Names listed in them.  The problem is I have no way of knowing if Table1 will hold more names or if Table2 will hold more names so I was thinking a union would solve this issue.  However as you see in the syntax below it produces to lines for "C" since it exists in both tables.  This is my desired output

empName | TotalSW | TotalSNT
--------+---------+---------
A       |       1 |       0
B       |       1 |       0
C       |       1 |       1
x       |       0 |       1
z       |       0 |       1

With the DDL Below how can a query be written to produce this output?

Create Table #Test1
(empName varchar(100), swas varchar(100))

Create Table #Test2
(empName varchar(100), swont varchar(100))

Insert Into #Test1 (empName, swas) VALUES
('A', 'res1'), ('B', 'tim1'), ('C', 'run34')

Insert Into #Test2 (empName, swont) VALUES
('C', 'er12'), ('z', 'nn12'), ('x', '23rw')

Select empName, TotalSW = COUNT(swas), TotalSNT = ''
FROM #Test1
GROUP BY empName
Union
Select empName, TotalSW = '', TotalSNT = Count(swont)
FROM #Test2
GROUP BY empName


texts: SQL Server: Rows over 8KB: Where are the docs?

texts:
I need to properly understand if and how I can get 10KB or 20KB per row in SQL Server 2016, Standard Edition.

I am reading the SQL Server Tech specs:

https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server#Engine

Specifically "Bytes per row"

It refers to something in BOL, that I cannot find.

Where is the section "Row-Overflow Data Exceeding 8 KB" topic in SQL Server Books Online. ?


texts: Right Trimming Binary Data in SQL Server

texts:
Scenario:

I am inserting a string into a binary field (CONTEXT_INFO) and then later attempting to pull it out and convert it back to a string.  When I do, the resulting string has a length of 128 because it has trailing null characters.

Example:

DECLARE @string VARCHAR(128)
DECLARE @binary VARBINARY(128)

SET @string = 'abcdefg'
SET @binary = CONVERT(VARBINARY(128), @string) --0x61626364656667000000...
SET CONTEXT_INFO @binary
SET @binary = CONTEXT_INFO()

-- I would like to change the following line so it trims trailing null chars
SET @string = CONVERT(VARCHAR(128), @binary)

SELECT
    @binary AS [binary],
    DATALENGTH(@binary) AS [binary.Length], --128 as expected
    @string AS [string],
    DATALENGTH(@string) AS [string.Length] --This is 128, but I need it to be 7

Question:

How can I trim the trailing null characters when I convert the binary field to a string?


texts: Launch a stored procedure when a datetime is expired

texts:
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.


texts: How to format the output of SQL queries on SQLCMD for SQL Server 2017 on RHEL Linux 7.3?

texts:
I have installed SQL Server 2017 on RHEL Linux 7.3, and using SQLCMD utility for querying the SQL Server.

When I try to query for example: 'SELECT * FROM SYS.DATABASES'. I get output like the following:

How to format the output for SQL commands on SQLCMD for SQL Server on RHEL Linux?

For example, in Oracle we use 'SET LINES 200 PAGES 200' to format the output / width of the output columns.

Note: I have already tried the following workaround (as per other answer on dba.stackexchange):

C:/> sqlcmd -S my_server
:setvar SQLCMDMAXVARTYPEWIDTH 30
:setvar SQLCMDMAXFIXEDTYPEWIDTH 30
SELECT * from my_table
GO


texts:
I have a table with the following structure:

create table pairs (
   block text,
   left_entity int,
   right_entity int
);

That table was the result of a record linkage process. One of the particularities of the table is that left_entity < right_entity.

An example of the data inside the table is:

insert into pairs values
( 'A' , 31 , 36 ),
( 'A' , 31 , 34 ),
( 'A' , 33 , 39 ),
( 'A' , 32 , 33 ),
( 'A' , 32 , 39 ),
( 'A' , 36 , 37 ),
( 'A' , 34 , 37 ),
( 'A' , 34 , 36 ),
( 'B' , 47 , 49 );

Every pair on this table is considered the same entity.

My goal is to aggregate in an array all the pairs that belong to the same entity. So, for the example above

|A| {31, 34, 36, 37} |
|A| {32, 33, 39 }    |
|B| {47,49}          |

etc

I think that the approach should be a recursive CTE or a LATERAL JOIN (a similar problem is here), but I can't figure out how to break the loop (since the CTE solution assumes a relationship parent-child) or how to code the function for the LATERAL JOIN (the example assumes that there is only one, but trying that function with this data returns several rows).
(It seems that the problem is that the data has a graph like structure, instead of the hierarchical structure assumed in the examples).

Any help will be appreciated.


texts: Windows Authentication In SQL DTS packages

texts:
We have a SQL server 2008 R2 and have legacy DTS packages running on the server. As of now, the subtasks inside DTS packages are using dynamic properties where SQL Login ID details have been given, which are used by the subtasks to login to another server in same domain and get the results from databases for processing.

Our need is to get rid of SQL logins and start using Windows Authentication for all the processes inside the DTS packages, which includes the subtasks to connect another SQL instance. However, I couldn't find any know way or option in the dynamic properties file to make use of Windows Authentication mode.


texts: SQL Server transactional replication issue with database

texts:
We have on the server a database and we want to replicate a database on another server. we've set a transactional replication, but we have issues with the default constraints on the tables:

On the left is schema of the table from a backup from the original server and on the right is the schema from the replicated backup. Is there some setup on the replication so that the constraints remain the same on both databases? I've used Sql Schema Compare from visual Studio 2013 to compare the databases.


texts: MySQL Server Install Setup Types Developer Default vs Full

texts:
As I'm installing MySQL Server Community Edition the possible Setup Types are:

Developer Default
Server Only
Client Only
Full
Custom

Is there any difference between options Developer Default and Full? As I get to the lists of products to be installed (MySQL Server, MySQL Workbench, Connectors etc.) they seem identical.


texts: Importing CSV file to MySQL via PHPMyAdmin

texts:
I am currently stuck and unable to upload a complete CSV file into a MySQL database. Unfortunately, the nice folks at Stackoverflow haven't been able to help me solve this issue, so I'm hoping someone here may be able to assist me.

I would like to upload a CSV file into a MySQL database programmatically using PHP and MySQL's LOAD DATA INFILE function. This is the code that is auto-generated from PHPMyAdmin's import page:

"LOAD DATA LOCAL INFILE  '..path/property_re_1.csv' REPLACE INTO TABLE  `markers` FIELDS TERMINATED BY  ',' ENCLOSED BY  '"' ESCAPED BY  '\\' LINES TERMINATED BY  '\r\n'";

The problem is the CSV file only loads until row 44, where there is a text string with quotations.

I have included the actual SQL table and CSV file for debugging purposes since the information used in this case is public information. Feel free to download it:

https://www.dropbox.com/sh/4iq10i51qlqyq8q/UjEQwvXKDA

Ultimately I am looking for a PHP code with the proper SQL function for this task, as I will be running the PHP script routinely to update the database on the server.

Thanks in advance!


texts: Is there a public repo where I can contribute to the MySQL .NET connector?

texts:
We have found ourselves making some performance and scale improvements for the MySQL .NET driver from here http://dev.mysql.com/downloads/connector/net/

We know where to get the source in a ZIP archive. But is there a proper source repo that we can contribute to? A google search yields a blank.


texts:
I have an assignment at the university and it is about decomposition. These are the relation and FDs.

V={ABCDEF},F={AB→CD,ABDE→F,BC→A,C→DF}

We need to find candidate keys, canonical cover(basis) and decomposition in 3NF with dependency preservation and lossless-join.

-I found that our candidate keys are: {ABE, BCE}

-Basis=  {AB→C,BC→A,C→DF}

-But I am not really sure about the decomposition in 3NF and really confused. This is my solution:

Minimal Basis R0= {AB→C,BC→A,C→DF}
Step 1: Generation
R1= (ABC)
R2=(BCA)
R3= (CDF)
Step 2: If none of the relation schemas in Ri contains a candidate key of R0 Create new relation schema Rj contains attributes form a key of R0

Candidate Keys={ABE,BCE}
R4= (ABE)
R5= (BCE)

Step 4: Eliminate relational schemas that are included in others (R1 and R2)
Result: Z= {R1, R3, R4, R5)

Do R4 and R5 relations make sense? What is the accurate decomposition in 3NF?


texts: Installing and configuring postgresql 9.3 on ubuntu 13.10 using puppet

texts:
I'm having some trouble getting postgresql[1] to install with puppet[2] on a blank ubuntu box.

I need to install postgresql 9.3 on Ubuntu 13.10. Since postgresql 9.3 is apparently not in the official repository, I need to add some sort of key (apt-key add )[3].

I'm a developer so I don't often setup such systems from scratch and now I'm having to find the right set of documents for apt, postgresql, puppet and ubuntu. However, this stack is popular enough that it should be pretty common.

Any one have steps I can follow to install pgsql 9.3 on a blank ubuntu 13.10 box (using puppet)? I'm willing to blow away my existing vm and re-install 13.10 on my virtualbox to make sure I have a perfectly standard ubuntu server installation. As long as I can re-produce this setup again and again in a declarative manner, I don't mind things like installing from source, etc (although I prefer binary packages).

[1]https://forge.puppetlabs.com/puppetlabs/postgresql

[2]https://forge.puppetlabs.com/puppetlabs/apt

[3]https://wiki.postgresql.org/wiki/Apt


texts:
(Oracle) I'm trying to do an exchange partition on an empty partitioned table to fill it with data from a non-partitioned table. I'm getting the following error and can't find the cause. I'm not 100% certain of the version, I know it < 11g. I'll have to double check and update my post.

SQL Error: ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
14097. 00000 -  "column type or size mismatch in ALTER TABLE EXCHANGE PARTITION"
*Cause:    The corresponding columns in the tables specified in the
           ALTER TABLE EXCHANGE PARTITION are of different type or size
*Action:   Ensure that the two tables have the same number of columns
           with the same type and size.

What I have tried

Verified both tables have same number of columns, order of columns, data types, constraints and indexes
Verified there are no hidden columns on my newly partitioned empty table
I have not altered the original table in any way shape or form
I tried making a copy of the original table and doing the exchange partition with that on the off chance something may be writing to the original table and Oracle doesn't like that.
I tried using Dbms.Redefine, but don't have the rights to run that and it's not an option for the DBA to grant me rights to that.

Code

Create copy of original table:

create table foo as select * from LOGGING_EVENT

Creating partitioned table

create table LOGGING_EVENT_PARTITIONED
(
  TIMESTMP number(20,0) not null,
  FORMATTED_MESSAGE varchar2(4000 BYTE),
  LOGGER_NAME varchar2(254 BYTE) not null,
  LEVEL_STRING varchar2(254 BYTE) not null,
  THREAD_NAME varchar2(254 BYTE),
  REFERENCE_FLAG number(38,0),
  ARG0 varchar2(254 BYTE),
  ARG1 varchar2(254 BYTE),
  ARG2 varchar2(254 BYTE),
  ARG3 varchar2(254 BYTE),
  CALLER_FILENAME varchar2(254 BYTE) not null,
  CALLER_CLASS varchar2(254 BYTE) not null,
  CALLER_METHOD varchar2(254 BYTE) not null,
  CALLER_LINE char(4 BYTE) not null,
  EVENT_ID number(10,0) primary key
)
partition by range (event_id)
interval(10000)  (
  partition p1 values less than (10000)
);

Exchanging partition:

alter table LOGGING_EVENT_PARTITIONED
exchange partition p1
with table foo
without validation
update global indexes;

Error:

SQL Error: ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
14097. 00000 -  "column type or size mismatch in ALTER TABLE EXCHANGE PARTITION"
*Cause:    The corresponding columns in the tables specified in the
           ALTER TABLE EXCHANGE PARTITION are of different type or size
*Action:   Ensure that the two tables have the same number of columns
           with the same type and size.

Verifying original and partitioned tables are the same

New Partitioned Table

Original Table


texts: SQL Server 2008 log file growing and wont shrink. Hard Drive Space running out

texts:
I have a SQL Server 2008 database which has a log file (.ldf) with a size of 360GB and growing every day.

The .mdf file is only 25GB. The hard drive is 950 GB and has 150GB free space. I want to shrink this log file so that it doesn't overrun the hard drive on the server.

I used dbcc shrinkfile('my database log file name', 0) but unfortunately, it doesn't shrink the file. Now I know I need to take a log backup and then run the shrink command for the file to shrink.

My problem is that I don't have enough hard drive space to take a log backup.

Is there anyway I can shrink this file?

The database is on FULL RECOVERY MODE with mirroring ON. Please advise. Any tips to avoid this problem in the future would also be appreciated.


texts: PostgreSQL: create user who can create new roles?

texts:
I would like to create a user who can create other roles.
For example: user "dennis" can create roles.
User "dennis" owns database "main" and can only access this database.
Would it be possible to for "dennis" to create a role, which can access other databases?


texts: How to quantify TEMPDB usage in relation to other databases hosted on same hardware?

texts:
Working on a project to decide if configuration 1 or 2 is the best way to organise disks for my SAN:

Config1 (sql 2008):

8 disks @ raid 10 data
2 disks @ raid 1 logs
2 disks @ raid 1 tempdb
2 disks @ raid 1 hyper-v

(based on SQL best practice suggesting isolating tempdb)

Config2 (sql 2008):

8 disks @ raid 10 data + tempdb mdf
4 disks @ raid 10 logs + tempdb logs
2 disks @ raid 1 hyper-v

Current setup (sql 2005):

8 disks @ raid 10 data + tempdb mdf
4 disks @ raid 10 logs + tempdb logs

Under current setup windows performance montior confirms :

MDF writes spike , reads consistent
LDF writes consistent, reads non-existent

Using the following query (most accessed tables per database) i can accumulate the "accesses values" and compare the usage of each database relative to each other:

SELECT
DB_NAME(ius.database_id) AS DBName,
OBJECT_NAME(ius.object_id) AS TableName,
SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) AS TimesAccessed
FROM sys.indexes i
INNER JOIN sys.dm_db_index_usage_stats ius
ON ius.object_id = i.object_id
AND ius.index_id = i.index_id
WHERE
ius.database_id = DB_ID()
GROUP BY
DB_NAME(ius.database_id),
OBJECT_NAME(ius.object_id)
ORDER BY SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) DESC

What i am finding is the following:

db.2 = 8,943,628,393

db.S = 360,805,802

db.L = 660,469

db.tempDB = 173,094

Not sure this is a good way to quantify the usage of tempDB however. The script looks at tables but tempDB doesnt contain tables. I understand its a caching mechaism but how does it work and how can i quantify it in relation to my other DBs to help me make a decision on disk layout.

Thank you

Scott


texts:
I have a table named SKILLS with two columns: id and skills.
id is the id of the user, skill is a particular skill.

I need to find other users who have similar interests, like who has the most similar skills to the current user.

How do I structure the join to find such users?


texts: Rebinding a SQL Server user to its login after a restore with this user

texts:
After performing a restore of a SQL Server (v12.0.2000) database, I figured that a user needs to be remapped to its login (this issue seems to be known as orphan users). And one of the simplest way to do it is to perform a :

use [MyDatabase];
alter user MyUser with login = MyUser;

Nevertheless, just after achieving a restore, the USE command fails because I'm trying to execute the above command with 'MyUser', which is the one that is orphaned...

Because it is in a script, I would like to avoid performing a restore with one user and a alter user with another user, in order the first user be correctly remapped to its login.

Is there a way to do this with 'MyUser' (who could connect to master without problem) ?

I tried it by fully qualifying 'MyUser' the following way without success :

use master;
alter user MyDatabase.MyUser with login = MyUser;

Any idea if this is possible and how to do it ?


texts: Are there other sites still using SQL Server with codepage 850 or codepage 437 for their sql scripts?

texts:
Using one of these codepages is a disaster when scripts are edited by Visual Studio or TFS, but sometimes it is difficult to change old standards.

What must be observed, when changing to the current windows defaults?


texts: RMAN DUPLICATE from backup without Target or Catalog: How is the aux instance created?

texts:
I am trying to duplicate an Oracle database based on backups to test if they can be used in case of complete system failure. Hence, I want to do this without a connection to the source database. I'm not using a Recovery Catalog.

I've read through all of the manual, but am stuck at step 7 of "Preparing to duplicate a database" on the fourth point:

  Start SQL*Plus and connect to the auxiliary instance as a user with SYSDBA or SYSBACKUP privileges.

The auxiliary server I am duplicating to is a clean empty installation with no database set up yet, as the manual mentions at Step 3:

  Install an Oracle Database that is the same release as the source database. Do not create a database; install the software only.

Now, when running

sqlplus "/ as sysdba"

I get

ORA-12560: TNS:protocol adapter error

Not entirely unexpected, as at no point have I set up a listener or Oracle service. So... what did I mis? Did I misunderstand the part about not creating a database? Is "a database" not the same as "an instance"? Did I overlook a part of the manual? And, what should I still install/create in order to have an "aux instance" i can start in nomount?

EDIT: This is in a Windows-server environment


texts: How to return a varchar type in a select of a free string. example: Select 'Test' as field from table

texts:
Unlike other databases, PostgreSQL returns a TEXT type by default every time you run a select with a free string. Example:

SELECT 'Text' as FIELD FROM TABLE

in this case above the field FIELD will be returned with type TEXT

Does anyone know if it is possible to change some parameter in PostgreSQL so that it returns a CHAR or VARCHAR type in the above case?

Firebird and Oracle return a CHAR type.

I know this would be solved with a CAST however for this I would have to replicate the same for all SQL's of the system besides having to make using CAST a standard routine of developing new SQL's.

Anyway, does anyone have any idea of anything that might have changed? I'm studying the files pg_cast.h and pg_type.h


texts: Importing XML Data into SQL Server Table

texts:
So after searching on here, I couldn't find an answer to this problem I am having.

I am trying to import StackExchange's DB Dump into SQL Server to do some testing and learning overall. However, they come in this format:

    <?xml version="1.0" encoding="utf-8"?>
<votes>
  <row Id="1" PostId="1" VoteTypeId="2" CreationDate="2010-07-20T00:00:00.000" />
  <row Id="2" PostId="1" VoteTypeId="2" CreationDate="2010-07-20T00:00:00.000" />
  <row Id="3" PostId="2" VoteTypeId="2" CreationDate="2010-07-20T00:00:00.000" />
  <row Id="4" PostId="2" VoteTypeId="2" CreationDate="2010-07-20T00:00:00.000" />
</votes>

Now, importing through the wizard didn't work. So here I am trying to find a way to write code or do something to import this.

The thing is, the data isn't separated by tags, but complete rows. So instead of having:

<Posts>
<id> 1 </id>
<PostId> 1</PostId>
</Posts>

I have that thing above. So I can't exactly just take the tags and their values with something like @xData.value etc... Which is the solution I found on other posts here.

Maybe the solution is much simpler and I am missing something, donno. Your help would be appreciated


texts: Updating large table worked before, now taking hours

texts:
BACKGROUND: I am using a non-production SQL Server to manipulate a large data set and run a few basic calculations. The original data file I have, which is about 30M rows, is missing records (not NULL; they are non-existent, but for reasons beyond my judgement they must be there). So I created essentially a "shell" table for all rows and columns (6 total columns) that should be there- this one is about 80M rows.

PROBLEM: The step I need help with is overlaying the original 30M rows on top of the 80M row table. (All 30M rows have a matching record in the larger table based on the values of three columns, and the remaining three columns, currently null, need to be overwritten with the original data.)

I successfully did this process for a larger dataset (40M/100M tables) and the process took about 4 hours. That's fine- it's a one-shot thing. But when I run it on this smaller data set, it runs for 10+ hours. The first time I got a time out error. I've tried adjusting a few things and running it twice more, but I've cancelled the query after about 10 hours thinking I could try something else.

Any thoughts on why it worked the first time for a larger table, but not on this smaller one? Any thoughts on how I can make the code more efficient or clear out something in a file somewhere?

CODE:

update [shelltable]
set shelltable.column4 = originaltable.column4,
    shelltable.column5 = originaltable.column5,
    shelltable.column6 = originaltable.column6
from shelltable left join original table
  on shelltable.column1 = originaltable.column1 and
     shelltable.column2 = originaltable.column2 and
     shelltable.column3 = originaltable.column3;

Things I've tried since the unsuccessful attempts:

Restarting the SQL Server instance so tembdb clears
Restarting the PC
Reducing the number of columns in the SET clause to 1 (I would re run for each column 4-6)
Update statistics on both tables
Ran DB Tuning Engine, which recommended and created an index on the larger table
Added 'WHERE originaltable.column4 is not null' to exclude writing on some of the rows

The latest attempt is still running after these adjustments and is currently at 9.5 hours. Thanks for your tips and insights!


texts: "Folder" structure of Postgres database missing in DBeaver

texts:
I guess I did a faulty database restoration or the dump did not have this data and the "folder like" structure was lost. This is a capture of Dbeaver showing what I mean with "foler like" structure. I can easily access the functions, tables, schemas, etc. stored in the database definition. I'm not so worried about the loss of the original structure as it did not have much, but I do want to recreate it step by step when I need it. For example, now I made a function and would like to see it under a "Functions" folder, how can I do that?

Also, what should I have included in the pg_dump generation script to also backup this structure?

Thanks.


texts: Export and Import large database from one rds of amazon to newly created amazon rds

texts:
We are facing some issue. We would like to export and import mysql database "xyz" from old rds say xyz-rds to newly created rds say abc-rds (mysql).

We are approaching common method taking full export of xyz database using mysql command and which is too much of time.

Actually database size is of 45GB . from one hour we exported only 2 GB  of data so , if we calculate it will take around 20 hours to export whole database. Which will be tedious, So please suggest any solution to import and export database as much quickly as possible. Like say maximum of 2 hours.

Note: We are running old rds with 40GB RAM and new one with 20 GB RAM.


texts: how to find the name of the distributor server and publisher server from the subscriber server/subscriber database

texts:
Having in mind that I can add computed columns to the subscription tables
according to this link:

SQL Server : Transactional Replication Computed Column

That I am already doing, and also adding indexes to replicated tables (in the subscriber server)

However,

While I am doing it, I have to face too many locks and blocks to/from the replication as you can see on the picture below:

this is the replication command I was blocking while creating my index:

create procedure [sp_MSupd_dbotblBGiftVoucher]
        @c1 binary(24) = NULL,
        @c2 smallint = NULL,
        @c3 varchar(20) = NULL,
        @c4 int = NULL,
        @c5 varchar(20) = NULL,
        @c6 int = NULL,
        @c7 bit = NULL,
        @c8 char(1) = NULL,
        @pkc1 binary(24) = NULL,
        @bitmap binary(1)
as
begin
if (substring(@bitmap,1,1) & 1 = 1)
begin

update [dbo].[tblBGiftVoucher] set
        [strVoucherNumber] = case substring(@bitmap,1,1) & 1 when 1 then @c1 else [strVoucherNumber] end,
        [sintMarketID] = case substring(@bitmap,1,1) & 2 when 2 then @c2 else [sintMarketID] end,
        [strIssuedBxOrderNo] = case substring(@bitmap,1,1) & 4 when 4 then @c3 else [strIssuedBxOrderNo] end,
        [lngIssuedUserID] = case substring(@bitmap,1,1) & 8 when 8 then @c4 else [lngIssuedUserID] end,
        [strReedemedBxOrderNo] = case substring(@bitmap,1,1) & 16 when 16 then @c5 else [strReedemedBxOrderNo] end,
        [lngRedeemedUserID] = case substring(@bitmap,1,1) & 32 when 32 then @c6 else [lngRedeemedUserID] end,
        [blnVoid] = case substring(@bitmap,1,1) & 64 when 64 then @c7 else [blnVoid] end,
        [strCheckDigit] = case substring(@bitmap,1,1) & 128 when 128 then @c8 else [strCheckDigit] end
    where [strVoucherNumber] = @pkc1
if @@rowcount = 0
    if @@microsoftversion>0x07320000
        exec sp_MSreplraiserror 20598
end
else
begin

update [dbo].[tblBGiftVoucher] set
        [sintMarketID] = case substring(@bitmap,1,1) & 2 when 2 then @c2 else [sintMarketID] end,
        [strIssuedBxOrderNo] = case substring(@bitmap,1,1) & 4 when 4 then @c3 else [strIssuedBxOrderNo] end,
        [lngIssuedUserID] = case substring(@bitmap,1,1) & 8 when 8 then @c4 else [lngIssuedUserID] end,
        [strReedemedBxOrderNo] = case substring(@bitmap,1,1) & 16 when 16 then @c5 else [strReedemedBxOrderNo] end,
        [lngRedeemedUserID] = case substring(@bitmap,1,1) & 32 when 32 then @c6 else [lngRedeemedUserID] end,
        [blnVoid] = case substring(@bitmap,1,1) & 64 when 64 then @c7 else [blnVoid] end,
        [strCheckDigit] = case substring(@bitmap,1,1) & 128 when 128 then @c8 else [strCheckDigit] end
    where [strVoucherNumber] = @pkc1
if @@rowcount = 0
    if @@microsoftversion>0x07320000
        exec sp_MSreplraiserror 20598
end
end

there are too many servers, and I am not sure of which server is the publisher,
where is the distributor database, and if there is a distributor server, which one is it?

Is there a way I can find the name of the publisher and distributor (server and database), from the subscriber?

some information is here:
How to restart the distributor agent of transactional replication?

but I am still struggling to pause the replication for at least one article, but I could pause it for the whole publication.

What I want to achieve is to pause the replication for my current subscription (the database where I am on)...


texts:
This just came up in a question on Reddit, and I'm wondering

What is a PARTITIONED OUTER JOIN in Oracle? (definition)
What does a simple example look like? (use)
How would you write it in terms of PostgreSQL or Standard SQL that otherwise lacks PARTITIONED OUTER JOIN? (equivalence)


texts: Combining data from multiple large tables

texts:
I have a customer survey dataset split across 25 different tables. I need to combine all the tables and produce one file with all the columns from all tables. All the tables have the SurveyID key in common.
For example

table1: SurveyID, Region, Gender

table2: SurveyID, AnswerNumber, Rating

table3: SurveyID, AnswerNumber, Expediture.

I want the output like this and don't want any duplicate rows:

Final table: SurveyID, Region, Gender, Rating, Expenditure...and so on.

One issue is that not all tables contain data for the same SurveyID. For example table 1 has 114000 rows whereas table 2 only has 50000 rows, indicating not everyone answered those survey questions.

I've looked through the site and tried using left joins and inner joins as per the suggestions but not getting the result I want.

UPDATE - Here is the code I tried based on the response:

;with surveys as
(
select * from dbo.vw_IVSSurveyMainHeader s
),
responses as
(
select s.SurveyID, t1.["AnswerNumber"], t1.["AccommTypeUsed"],
t2.["ActivityCategory"], t2.["Activity"], t2.["ActivitySubType"],
t3.["AirlineUsed"], t3.["AirlineRating"],
t4.["AppealReason"],
t5.["CycleOwnership"],
t6.["InformationSourceUsed"],
t7.["WebsiteForPlanning"], t7.["WebsiteForBooking"], t7.["UsefulnessRating"],
t8.["AmenityType"], t8.["EnvPracticeRating"],
t9.["ItemisedCostAmt"], t9.["ExpenditureType"], t9.["ItemisedCostCurr"],
t10.["WhereStayed"], t10.["NoNights"],
t11.["SatisfactionRatingReason"],
sat.["SatisfactionRating"],
t12.["SocialMediaUsage"], t12.["SocialMediaType"], t12.["TripStage"],
t13.["SocialMediaUsed"],
t14.["TechnologyType"],
t15.["TechnologyUses"],
t16.["TransportMethod"],
t17.["TravelledWith"]
from dbo.vw_IVSSurveyMainHeader s
left join dbo.vw_IVSAccommodationUsed t1 on s.SurveyID = t1.SurveyID
left join dbo.vw_IVSActivities t2        on s.SurveyID = t2.SurveyID
left join dbo.vw_IVSAirlineRating t3     on s.SurveyID = t3.SurveyID
left join dbo.vw_IVSAppealReasons t4     on s.SurveyID = t4.SurveyID
left join dbo.vw_IVSCycleOwnership t5    on s.SurveyID = t5.SurveyID
left join dbo.[vw_IVSDecisionMakingProcess - After arriving] t6 on s.SurveyID = t6.SurveyID
left join dbo.[vw_IVSDecisionMakingProcess - Planning and booking] t7 on s.SurveyID = t7.SurveyID
left join dbo.vw_IVSEnvironmentPracticeRating t8 on s.SurveyID = t8.SurveyID
left join dbo.vw_IVSExpenditure t9 on s.SurveyID = t9.SurveyID
left join dbo.vw_IVSItineraryPlaces t10 on s.SurveyID = t10.SurveyID
left join dbo.vw_IVSSatisfactionRatingReasons t11 on s.SurveyID = t11.SurveyID
left join dbo.vw_IVSSatisfactionRatings sat on s.SurveyID = sat.SurveyID
left join dbo.vw_IVSSocialMediaUsage t12 on s.SurveyID = t12.SurveyID
left join dbo.vw_IVSSocialMediaUsed t13 on s.SurveyID = t13.SurveyID
left join dbo.vw_IVSTechnologyTypes t14 on s.SurveyID = t14.SurveyID
left join dbo.vw_IVSTechnologyUses t15 on s.SurveyID = t15.SurveyID
left join dbo.vw_IVSTransport t16 on s.SurveyID = t16.SurveyID
left join dbo.vw_IVSTravelParty t17 on s.SurveyID = t17.SurveyID
)
select surveys.*, responses.*
from surveys su left join responses r
on su.SurveyID = r.SurveyID

This throws the error that the column prefix for surveys and responses does not match with a table name or alias name used.


texts: How to configure SSIS package to connect to oracle using oracle wallet? Specifically for using OLEDB to connect to oracle and load data to oracle tables.


texts: Enabling SSL in MariaDB running on Synology NAS

texts:
I am having problems with enabling SSL in MariaDB running on Synology NAS (DSM  version 6.0) using self signed certificates. I have modified the my.cnf file to point to certificate files and the DB seems to be loading the .cnf file ok, the ssl variables are listing the corect location of the cert files (verified over phpmyadmin) however it still says that ssl is diabled (variable have_ssl=disabled) and the DB is refusing incoming ssl connections. This is my first time working with Synology NAS so I have no idea what to try, so any help or suggestion is welcome.
Thank you in advance.

Edit: I tried contacting synology tech support but got answer that they do not provide any support for mariadb beyond just installing it.


texts: What are the performance implications of running multiple smaller DBs instead of a single larger DB on a server?

texts:
Our database architecture allows multiple 'customers' to exist in the same database, yet we split them into mulitple database for administrative reasons [patching, backup, etc.]
Question 1
What would the performance implications be if we consolidated the customers into a single DB?
Question 2
We current have multiple customer in each DB, but we may have 10 in each and say 5 database; so if we consolidated the DBs we'd just have one DB with 50 customers; would that make much difference to performance?


texts: Allow access to RackSpace database from Vagrant box

texts:
we have two developers using Vagrant (which has it's own internal ip like: 10.0.2.0/24). On RackSpace we have a percona database instance. We would like to allow read/write access to that database from those vagrant boxes. I've never done this from vagrant boxes and was hoping someone could lend support.

The below commands are what I've issued thus far:

sudo iptables -A INPUT -p tcp -s 10.0.2.0/24 --dport 3306 -m conntrack --ctstate NEW,ESTABLISHED -j ACCEPT

sudo iptables -A OUTPUT -p tcp --sport 3306 -m conntrack --ctstate ESTABLISHED -j ACCEPT

sudo invoke-rc.d iptables-persistent save


texts: SQL Server 2016 Standard Edition Replication

texts:
I have a single database running on SQL Server 2016 Standard edition in our primary datacenter. I would like to keep a live copy as close to realtime (1-2 minutes) in our disaster recovery datacenter so when the primary goes down we can redirect the traffic to DR. When the primary is back, changes in DR need to sync back to the primary.

From what I've researched peer-peer isn't available in the Standard edition. I found (and lost) a doc that discussed how to do this in Standard edition. It was limited to one database with one primary and one backup, yet I can't figure out what I searched to find it.

Any suggestions on best practice to accomplish this sync?


texts: Massive deviation of tablespace max_size compared to bytes used by segments on that tablespace

texts:
I just checked the size of our tablespaces, because the admins said that they've made them bigger after we've had a full tablespace yesterday and I've discovered something really strange.

I hope someone can shed some light on this situation.
Here's what I did:

1) I queried the size of a certain tablespace, let's call it 'XYZ' for this purpose. XYZ is the tablespace that was full yesterday and got an upgrade in size.

select (max_size / 1024 / 1024) as size_megabyte from user_tablespaces where tablespace_name = 'XYZ'

The result is 2048 MB.

2) I queried the segments using the tablespace 'XYZ' and summed up their used bytes

select sum(bytes)/1024/1024 as size_megabyte from user_segments where tablespace_name = 'XYZ'

The result is 36000 MB.

3) I queried the free space left on the tablespace 'XYZ'

select sum(bytes)/1024/1024 as size_megabyte from user_free_space where tablespace_name = 'XYZ'

The result is 4000 MB

At this point my jaw dropped, and I can't fathom what's going on here.
Does anyone have a clue? Are my queries faulty?

Judging by the results of 2) and 3), I'd expect query 1) to return 40000 MB and not 2048 MB.


texts: Configure MariaDB PAM authentication, and enabling remote login from Toad

texts:
I am currently trying to integrate Linux services into my Windows environment and have installed MariaDB with PAM authentication following the guidance from MariaDB.
I have tested successfully on the box (using putty) that I can authenticate using my AD credentials, however when I try to login via Toad I get the following error:
Client does not support authentication protocol requested by server; consider upgrading MariaDB client

I have tried adding the line pam-use-cleartext-plugin to the conf file and restarting the server but no joy.
Does anyone have any suggestions on how to resolve such that remote logins with Toad (and other clients) using AD credentials works?
Update
I have downloaded HeidiSQL and confirmed that its use of libmysql.dll means that it can use the PAM authentication.  It looks like toad for MySQL utilises myodbc.dll


texts: DB2 Load Utility Primary Key vs. Unique Index

texts:
In short, does the Primary Key double as a Unique Index?
Some background:
I am trying to load a bunch of data into two tables using the DB2 Load utility loading in IXF file types.  The data is separated out by table, and by month per table, so I need to run the load command repetitively.  The actual load of the data doesn't appear to take to long, however, the utility goes and rebuilds the indexes after each load.  This is progressively making my execution time longer and longer with each run as the tables grow.  In the DB2 documentation for the load utility, it shows a indexing mode of deferred option that sounds like it skips that step (recommending that you do a rebuild on the very last load).  When I set this option, I get the following error on execution for both tables...

SQL3214N  The load operation failed because the table is incompatible with the DEFERRED index mode. Reason code: "1".

Now I have gone through and looked at the syscat.indexes table and neither table has a "Unique Index".  Both have primary keys and only one has a unique key, which has prompted my question above.


texts: SQL Server express - Management studio installation hangs at native images installation

texts:
I've been trying to install SQL server express edition (2014) with management studio and consistently failing to do so because installation stalls at step 'Install_sql_common_core_Cpu64_Action : Write_NativeImage_64. Generating Native images'. I've tried several time but still it hangs at this point. I even left it overnight but no progress.

I've even tried installing SQL Server 2012 management studio installation file but same issue again.

I tried to search for this kind of issue but no discussion found anywhere on internet. Any clue or pointers will be greatly appreciated. I've been trying since last couple of days but no luck.

Other details:

OS: Windows 10 Pro
Visual Studio 2015 Community edition installed
All windows updates are up to date
Downloaded management studio from MS site and File version is 12.0.2000.8

If any other details needed please let me know.

See below image:


texts: MariaDB keeps crashing after a few hours with ridiculous memory expectations

texts:
Mariadb keeps crashing with typical errors from /var/log/mysql/error.log being:

... terribly wrong ...
stack_bottom = 0x0 thread_stack 0x20000
(my_addr_resolve failure: fork)
...
2019-01-27  9:04:30 3069758672 [ERROR] mysqld: Out of memory (Needed 130760704 bytes)
2019-01-27  9:04:30 3069758672 [ERROR] mysqld: Out of memory (Needed 98058240 bytes)
2019-01-27  9:04:30 3069758672 [ERROR] mysqld: Out of memory (Needed 73531392 bytes)
2019-01-27  9:04:30 3069758672 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2019-01-27  9:04:30 3069758672 [Note] InnoDB: The InnoDB memory heap is disabled
2019-01-27  9:04:30 3069758672 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-01-27  9:04:30 3069758672 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2019-01-27  9:04:30 3069758672 [Note] InnoDB: Compressed tables use zlib 1.2.8
2019-01-27  9:04:30 3069758672 [Note] InnoDB: Using Linux native AIO
2019-01-27  9:04:30 3069758672 [Note] InnoDB: Using generic crc32 instructions
2019-01-27  9:04:30 3069758672 [Note] InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(138543104 bytes) failed; errno 12
2019-01-27  9:04:30 3069758672 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2019-01-27  9:04:30 3069758672 [ERROR] Plugin 'InnoDB' init function returned error.
2019-01-27  9:04:30 3069758672 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2019-01-27  9:04:30 3069758672 [Note] Plugin 'FEEDBACK' is disabled.
2019-01-27  9:04:30 3069758672 [ERROR] Unknown/unsupported storage engine: InnoDB
2019-01-27  9:04:30 3069758672 [ERROR] Aborting

My platform is a Raspberry Pi Zero with 512M of RAM and running 2018-10-09-raspbian-stretch.  I’ve installed Mariadb version
mysql  Ver 15.1 Distrib 10.1.23-MariaDB, for debian-linux-gnueabihf (armv7l) using readline 5.2

From reading various forums I’ve changed the following:

/etc/mysql/mariadb.conf.d/50-server.cfg:
change key_buffer_size to 64K
change max_connections to 10
change query_cache_limit to 16K
change query_cache_size to 128K

I tried to add

performance_schema=0

to /etc/mysql/my.cnf but Mariadb didn’t recognize the variable.

There are only two clients to the database – an Apache2 server on the same device which connects every 5 seconds and a daemon I wrote in C which connects every second, both for a brief time.  I’ve checked my code and each of them closes the connection to Mariadb correctly.

Any help would be very much appreciated.

EDIT: After enabling Slow Query Logging, I find that most queries take around 1ms-3ms to run, but in the lead-up to the crash, this time began to ramp up to 1.8 seconds, before the final log event of:

SET timestamp=1548626761;
SELECT * FROM timers;
# Time: 190128  6:06:03
# User@Host: pi[pi] @ localhost []
# Thread_id: 4  Schema: hptimer  QC_hit: No
# Query_time: 1.865374  Lock_time: 0.801896  Rows_sent: 0  Rows_examined: 8
# Rows_affected: 1
SET timestamp=1548626763;
UPDATE relays SET state=0 WHERE id=3;
/usr/sbin/mysqld, Version: 10.1.23-MariaDB-9+deb9u1 (Raspbian 9.0). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument

then no further information.

EDIT#2: Thank you for the suggestions to monitor memory usage/other processes.  I found my C daemon process was consuming an additional 100KiB per call using "top", hence the OOM.  I shall pull out my K&R and take a closer look at my code before blaming other applications again :)

Best regards,
Peter

EDIT#3: You must always use

mysql_free_result( res );

after a query in C after your query has been processed.

https://dev.mysql.com/doc/refman/8.0/en/mysql-free-result.html


texts: How can I set a random timestamp for a row based on a columns value in mysql?

texts:
I have an app that I built and found an issue with it.

Essentially there are created_at and ended_at timestamp columns.

The ended_at function in my app had a bug so now I have a few hundred thousand rows that have no ended at timestamp.

I want to go through each row and based on the created_at column, generate an ended_at timestamp that's anywhere from 5 seconds to 8 hours away.

This is what I came up with, I just want to make sure it's what I want before I run it on 500k rows.

UPDATE streams SET ended_at = FROM_UNIXTIME(
        UNIX_TIMESTAMP(created_at) + FLOOR(0 + (RAND() * 28800))
    )
    WHERE ended_at IS NULL LIMIT 1;

I have tested it and it appears to function correctly and i have backups of my database, but I just want to be 100% sure.

Thanks


texts: Select columns by file containing primary keys

texts:
I have a text file with primary keys. Each line contains one primary key. Now I want to select the rows based on the input file. I can not find the right option for psql to do this. What is the canonical way for PostgreSQL to do this job?
I know I can do
psql -c 'select * from table' db | grep -f primary_keys

But this dumps the whole database. How can I avoid it?


texts: I would like to launch a pg_dump command from a remote computer

texts:
I would like to launch the command: pg_dump.exe -h 255.255.255.255 -p 5432 -U postgres -F p -a -v -f "test.db.dump" mydb from a remote computer. Both the database and the remote computer are under xp.

I face two troubles:

when executed on the command line of the server, pgdump will first prompt for a password. How can I grant the password in the command line ? A quick look at the documentation of pg_dump doesn't obviously accept a flag to do that (though I may have missed it).
if both machines had been under linux I would have tried an SSH connection with Putty to execute some commands on the psql server... but with both machines being under windows, I don't have an idea where to start. Does the psqlODBC driver allow access to pg_dump ? Is there an equivalent to SSL for windows ?

Only to explain the context, indeed my main goal is to achieve a diagnostic script on the remote machine that will gather some meaningful information, including a dump of some or all tables of the postgresql base. The diagnostic script will the copy the dump into another place on the domain where all diagnostic results are gathered.

The script has to be executed on the remote machine, because it must also performs other operations on other computers connected to the domain that aren't the postgresql database.


texts: Is there a way to change the compaction throughput via startup options in Cassandra?

texts: We are currently running a Cassandra cluster in K8S containers as Stateful sets. We're considering exposing the config file but we would like to know if perhaps we can provide this value as a startup parameter.


texts: Get the deleted value using while looping through the columns

texts:
I wrote a trigger to do auditing.  To capture all the changes, I loop through the list of columns.  The problem I'm having (see code below), when I try to set the OldValue using the @fieldname, I get back the actual field name. I want to get the value, so I don't have to explictly name each field (like I did when I set the NewValue - this works perfectly). Trying to avoid this because, just like most databases, we have tables with several fields. I'm stuck with the syntax on this.  Would like to do something like:

@OldValue = (Select 'd.' + @fieldname from deleted)

Here's my code:

SELECT * INTO #ins FROM inserted
SELECT * INTO #del FROM deleted

SELECT  @TableName = N'TESTAUDITTABLE'
SELECT  @PKCols =  column_name
                    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
                    INNER JOIN
                    INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU
                    ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
                    TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME
                    and ku.table_name= @TableName
                    ORDER BY KU.TABLE_NAME, KU.ORDINAL_POSITION;

SELECT  @UserName = SYSTEM_USER
SELECT  @UpdateDate = {fn now()}

SELECT  @field = 0
SELECT  @maxfield = MAX(ORDINAL_POSITION)
        FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName

WHILE   @field < @maxfield
BEGIN
       SELECT @field = MIN(ORDINAL_POSITION)
               FROM INFORMATION_SCHEMA.COLUMNS
               WHERE TABLE_NAME = @TableName
               AND ORDINAL_POSITION > @field
       SELECT @bit = (@field - 1 )% 8 + 1
       SELECT @bit = POWER(2,@bit - 1)
       SELECT @char = ((@field - 1) / 8) + 1
       IF SUBSTRING(COLUMNS_UPDATED(),@char, 1) & @bit > 0 OR @Type IN ('I','D')
       BEGIN
               SELECT @fieldname = COLUMN_NAME
                       FROM INFORMATION_SCHEMA.COLUMNS
                       WHERE TABLE_NAME = @TableName
                       AND ORDINAL_POSITION = @field

                SELECT @ID = coalesce( i.ORIGREC , d.ORIGREC ) ,
                         @Type = case
                                       when i.ORIGREC is not null and d.ORIGREC is     null then 'Insert' -- insert
                                       when i.ORIGREC is not null and d.ORIGREC is not null then 'Update' -- update
                                       when i.ORIGREC is     null and d.ORIGREC is not null then 'Delete' -- delete
                                     end,
                 @OldValue = (Select @fieldname from deleted),
                 @NewValue = (Select i.NAME from #ins)

                FROM inserted i
                FULL join deleted d on d.ORIGREC = i.ORIGREC

               SELECT @sql = '
                    INSERT INTO [dbo].[LE_AUDIT] (
                        DB_USER,
                        TableName,
                        ID,
                        FieldName,
                        OldValue,
                        NewValue,
                        CHANGEDATE,
                        TRIG_TYPE)
                    VALUES (''' + SYSTEM_USER + ''',''' + @TableName +
                    ''',''' + @ID + ''',''' + @fieldname + ''',''' +
                    @OldValue + ''',''' + @NewValue + ''',''' +  @UpdateDate  + ''',''' + @Type + ''')'
               EXEC (@sql)

               print @sql
       END
END

The trigger works fine.  Here is the original source for the link.  https://www.simple-talk.com/sql/database-administration/pop-rivetts-sql-server-faq-no.5-pop-on-the-audit-trail/

I had to modify it to force it to work in conjunction with another app.  Just need to modify the one line to capture the value without specifying the actual field name in the while loop.


texts: How to store temp tables in memory during table "optimize"?

texts:
When optimizing tables, SHOW PROCESSLIST lists it as "copying to tmp table". I'm trying to ensure the copy takes place in memory to speed up the process. I've tried adjusting the tmp_table_size to accommodate the 5G table. However, I've seen zero performance gain. I've changed the temp directory to tmpfs, but again, zero gain. In fact, regardless of the settings I change, the tmp table always seems to be written to temp files, on disk, within the database's directory.

I assume data is written to disk in a table rotation manner for safety, but I'm wondering if there is anyway to direct the optimize process to create the temp table in memory instead?

Thanks!


texts: Query the last records by date, where condition is true

texts:
A made this query, which is works perfectly now:

SELECT t.DocID, t.RevID, t.EffDate
FROM tblRev AS t
INNER JOIN (
    SELECT DocID, max(EffDate) AS MAXEffDate
    FROM tblRev GROUP BY DocID
) AS tm ON (t.EffDate = tm.MAxEffDate) AND (t.DocID = tm.DocID);

Basically in my document database, All documents have revisions (1.0, 1.1, 1.2 etc.). I could query the last revisions by date, but there is an tblRev.OK column (true/false) in a table.

I need to insert a WHERE statement to query the last Revision where tblRev.OK=true.

For example(table):

 DocID - RevID - RevDate - OK
   1   -  1.0  -  jan 1  - true
   1   -  1.1  -  feb 2  - true
   1   -  1.2     march3 - false

So I wanted this line: 1   -  1.1  -  feb 2  - true

I can do it if i made query to list al Revs where=true, but I should do it in one query if its possible.


texts: How to model a database to retain driver related information?

texts:
I am modelling a chauffeur-like system, where for each driver that registers in the platform, he/she can either be a "solo" driver or belong to an already associated company.

Independently of the type of driver, the following information is registered:

Driver ID (Primary Key - I considered using this parameter in order to have the system more "ordered")
Name
Email (UNIQUE)
Phone Number (UNIQUE)
Registration Date (Time Stamp)

Now, if it's a "solo" driver, the following additional information will also be asked:

Vehicle type (for example: 'car', 'motorbike', etc.)
Plate number (UNIQUE)
Fiscal number (UNIQUE)

If he/she belongs to an associated company, only the name of the company will be asked (since all the vehicles will be associated to the car company).

The company table will have the following attributes:

Company ID (Primary Key - I considered using this parameter in order to have the system more "ordered")
Name
Address
Contact (UNIQUE)
Email (UNIQUE)
IBAN (UNIQUE)
Fiscal Number (UNIQUE)

Lastly, the vehicle table will have the following attributes:

Vehicle ID (Primary Key - I considered using this parameter in order to have the system more "ordered")
Company ID (Foreign Key)
Vehicle Type
Plate Number (UNIQUE)

A vehicle cannot exist as a standalone entity, i.e., it must be associated to a driver or to an existing company.

I would like to model this scenario using a relational DB since this info will be coupled with a routing algorithm. I'm having a difficult time finding a "clean" solution for this problem (specially considering that we are storing related-entity information, vehicle, in two different tables)... so, what's the best way to model this situation?

Responses to clarification requests

  In your business domain, is it possible for a solo driver to eventually work for a car company?
  Is it possible for a driver that works for a car company to become a solo driver only?
  Can a driver be working for a car company and, at the same time, be working as well as a solo dirver?

Regarding questions 1 and 2, those are cases I haven't quite considered, but they seem plausible possibilities, so we can consider that those are possible scenarios.

Regarding question 3, for the moment, let's consider that a person can only be a solo driver or a company driver.


texts:
I am quite new in MariaDB and I use SQLyog Community Edition as the frontend GUI. Whenever I create a new table, on the Engine dropdown, there is no XtraDB option. Is there any additional steps I am missing? As I've read, XtraDB is the InnoDB improved. How can I set my table to have that engine?

Here is my SHOW ENGINES result:

MariaDB [(none)]> SHOW ENGINES;

    +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
    | Engine             | Support | Comment                                                                    | Transactions | XA   | Savepoints |
    +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
    | MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                  | NO           | NO   | NO         |
    | CSV                | YES     | CSV storage engine                                                         | NO           | NO   | NO         |
    | PERFORMANCE_SCHEMA | YES     | Performance Schema                                                         | NO           | NO   | NO         |
    | BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears)             | NO           | NO   | NO         |
    | MyISAM             | YES     | MyISAM storage engine                                                      | NO           | NO   | NO         |
    | MRG_MyISAM         | YES     | Collection of identical MyISAM tables                                      | NO           | NO   | NO         |
    | ARCHIVE            | YES     | Archive storage engine                                                     | NO           | NO   | NO         |
    | FEDERATED          | YES     | FederatedX pluggable storage engine                                        | YES          | NO   | YES        |
    | InnoDB             | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
    | Aria               | YES     | Crash-safe tables with MyISAM heritage                                     | NO           | NO   | NO         |
    +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+


texts: What are the side-effects of disabling nested loops in postgres?

texts: A query of multiple joins spends a lot of time on nested loops. I disabled the nested loops and the query ran much faster. However, I have been told that nested loops might give me wrong results by skipping some tuples. Is this true?


texts: Updating column from another table - PostgreSQL 9.2

texts:
There are two tables:

ja_jobs
junk.test_test1

I need to update the time_job column from junk.test_test1 to ja_jobs.

Here is what I'm doing now:

UPDATE ja_jobs t2
SET time_job = t1.time_job
FROM junk.test_test1 t1
WHERE t2.id = t1.id
AND t2.time_job IS DISTINCT FROM t1.time_job;

But I'm getting empty/null updates..

Why is that happening and how can I solve the problem?

UPDATE 1:

When I run the query above, I get:

UPDATE 2202

So I check if the data has been changed:

select * FROM public.ja_jobs WHERE id = 14574527

And the time_job field is null - So no changes have been made

14574527 = Is an ID from a random ROW that has the time_job field null

UPDATE 2:

select count(*) FROM junk.test_test1 a
JOIN ja_jobs as b ON a.id = b.id
WHERE b.clientid = 23856 AND deleted = false

I get: 770 - So it seems that there are data to be updated.. But no update is happening...

QUERY:

UPDATE public.ja_jobs t2 SET time_job = t1.time_job FROM junk.test_test1 t1 WHERE t2.id = 14571727 AND t2.time_job IS DISTINCT FROM t1.time_job;

select id,clientid,time_job FROM public.ja_jobs WHERE id = 14571727

select id,clientid,time_job from junk.test_test1 WHERE id = 14571727

AS you can see, the test_test1.time_job is not null. That data should be updated into the public.ja_jobs.time_job column - But it's not

UPDATE 3:

Triggers:
    "b_iu_timestamps_from_visits" BEFORE INSERT OR UPDATE ON "ja_jobs" FOR EACH ROW EXECUTE PROCEDURE "tf_ja_jobs_timestamps_from_visits"()
    "ct_a_iu_jobs_same_account_only" AFTER INSERT OR UPDATE ON "ja_jobs" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "dm"."tf_jobs_same_account_only"()
    "ct_a_u_jobs_block_immutable_columns_changes" AFTER UPDATE ON "ja_jobs" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "dm"."tf_jobs_block_immutable_columns_changes"()
    "update_modified_date" BEFORE UPDATE ON "ja_jobs" FOR EACH ROW EXECUTE PROCEDURE "update_lastmodified_column"()


texts:
I have a database with three file groups, Primary, FG_ExtendedTables, and FG_ReportingTables.

I need to create another database (production db let's say), and restore only specific file groups and its tables with its data

I've seen this answer on stackoverflow https://stackoverflow.com/questions/25841822/backup-and-restore-sql-server-database-filegroup
but he's restoring the .bak file, I did like the example he provided but the bak file has all tables and I don't want that.

is that even possible ? if no, what is the purpose of the file groups then ?

I appreciate if code is provided, thank you.


texts: Limit crosstab query based on join from outer query

texts:
In my database I have two tables, assessments, and responses, in a 1:N relationship.

I have a query that looks a little something like this

  WITH "cross_responses" AS (select * from crosstab(
       $$select
         assessments.id as id,
         i.name as name,
         r.value as response
       from assessments
         left join users u on u.id = assessments.user_id
         inner join responses r on r.assessment_id = assessments.id
         inner join items i on i.id = r.item_id
       where i.name = ANY ('{E-mail Address, First Name, Last Name}')
       order by 1,2$$,
       $$VALUES('E-mail Address'), ('First Name'), ('Last Name')$$
     ) as ct(id int, email text, first_name text, last_name text)
) SELECT  ...blah blah...
ORDER BY "assessments"."id" desc, assessments.end_time DESC NULLS LAST,
assessments.start_time DESC NULLS LAST LIMIT 1000 OFFSET 4000

So basically, I'm creating a CTE made up a crosstabbed results for key responses from the assessment, then joining it two the assessments table, and selecting to get a nice flat representation mixing meta-data from the assessment, with the answers to individuals questions.

As you can see at the bottom, this query gets run in batches of 1000 when we are streaming a CSV file of all the results over HTTP.

I believe this results in a potential performance issue, because for each batch, I'm assuming crosstab will run against the entire assessment and responses tables, not against just the batch of defined by the outer query. Running explain analyze seemed to confirm this for as the crosstab line in the explain output, says it runs over 5080 rows, which is all the assessments in my development database (production has a lot more)

What I'm asking 1) Is my understanding about how crosstab behaves correct? 2) Is there a way to make the query passed into cross tab "aware" of limitations or conditions applied to the outer query? 3) Is there another approach I should be taking entirely to performantly run this query in a batched fashion to stream the data out to CSV.

EDIT: Adding the results of explain analyze

 Limit  (cost=1813.08..1813.08 rows=1 width=124) (actual time=71.913..72.036 rows=1000 loops=1)
  CTE cross_responses
    ->  Function Scan on crosstab ct  (cost=0.00..10.00 rows=1000 width=100) (actual time=63.593..64.074 rows=5080 loops=1)
  ->  Sort  (cost=1800.58..1803.08 rows=1000 width=124) (actual time=71.568..71.817 rows=5000 loops=1)
        Sort Key: assessments.id, assessments.end_time, assessments.start_time
        Sort Method: quicksort  Memory: 861kB
        ->  Hash Join  (cost=1712.00..1750.75 rows=1000 width=124) (actual time=66.358..69.842 rows=5080 loops=1)
              Hash Cond: (cross_responses.id = assessments.id)
              ->  CTE Scan on cross_responses  (cost=0.00..20.00 rows=1000 width=100) (actual time=63.595..65.204 rows=5080 loops=1)
              ->  Hash  (cost=1645.89..1645.89 rows=5289 width=28) (actual time=2.751..2.751 rows=5289 loops=1)
                    Buckets: 1024  Batches: 1  Memory Usage: 289kB
                    ->  Seq Scan on assessments  (cost=0.00..1645.89 rows=5289 width=28) (actual time=0.008..1.879 rows=5289 loops=1)
Total runtime: 72.384 ms


texts: With OPTION RECOMPILE vs without OPTION RECOMPILE

texts:
I am lost why execution plan is different if I run query with option recompile to compare to same query (with clean proc cache) without option recompile. The result is one row. The script is:

Create two tables:

CREATE TABLE H (id INT PRIMARY KEY CLUSTERED IDENTITY(1,1), header CHAR(100))
CREATE TABLE D (id INT PRIMARY KEY CLUSTERED IDENTITY(1,1), idH INT, detail CHAR(100))
CREATE INDEX IX_idH ON dbo.D(idH)

Fulfill:

DECLARE @i int = 1
WHILE @i <= 10
BEGIN
    INSERT INTO H (header) VALUES ('nonononono')
    SET @i = @i + 1
    PRINT @i
END
SELECT * FROM H
GO

DECLARE @i int = 1
DECLARE @ii int = 1
WHILE @i <= 7
BEGIN
    WHILE @ii <= POWER (10,(@i - 1))
    BEGIN
        INSERT INTO D (idH, detail) VALUES (@i, 'nonononono')
        SET @ii = @ii + 1
    END
    SET @i = @i + 1
    SET @ii = 1
END

The statement:

DBCC FREESESSIONCACHE

DECLARE @hid INT = 1

SELECT *
FROM H h
JOIN D d ON d.idH = h.id
WHERE h.id = @hid
OPTION (RECOMPILE)

With OPTION (RECOMPILE) it uses the key lookup for the D table, without it uses scan for D table. I do not understand why. MS SQL version 13.0.4411.0. And simple (7 rows) and actual statistics.


texts: Transaction Log Maintanance While Using AlwaysOn Availability Group

texts:
We are using HADR ( AlwaysOn Availability Group) feature of SQL Server 2012.
Server and AG Configuration as below:

SQL12_SRV1
--> AG1(PRIMARY)  - SYNC
   -->> DBTest
SQL12_SRV2
--> AG1(SECONDARY)
   -->> DBTest   - ASYNC

DBTest Database is growing (200GB) Day to day approximate monthly and same Transaction Log File will also grow according to Data.

So How to minimize Transaction Log File Size by using proper way of taking LOG backup.
On which Replica we have to take log backup.

Thanks In Advance.


texts: What are the top 3 performance Issues that you encounter with your SQL Servers?

texts:
I am a student from Fontys University in Eindhoven, and I am currently carrying out a series of interviews to help with the development of a SQL Server tool and I would like to get feedback from the experts in the field.

One of my questions is:

What are the top 3 performance issues that you encounter with your SQL Server Instances and how do you identify those problems?

Particularly I am interested in the scripts and the tools used to measure this.


texts: Syntax error when a job is executed in anonymous block in Oracle

texts:
I'm trying to execute a job with an alter system command but I get a error. I know that the error is in SID=''INST1'''', but I don't know how to avoid it.
I can create the job but the error is when the job is executed.

This is the code of the job:

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name             => 'JOB_Cambia_Planes_Prueba',
   job_type             => 'PLSQL_BLOCK',
   job_action           =>
'BEGIN
    EXECUTE IMMEDIATE ''ALTER SYSTEM SET resource_manager_plan=PLAN1 SCOPE=BOTH SID=''INST1'''';
exception when others then dbms_output.put_line(sqlerrm);
END;
',
   start_date           =>  TO_DATE('16-12-2019 17:33','DD-MM-YYYY HH24:MI'),
   repeat_interval      => 'FREQ=DAILY',
   enabled              =>  TRUE,
   comments             => 'Cambiar Planes');
exception when others then dbms_output.put_line(sqlerrm);
END;

And the error is

ORA-06550: line 2, column 89:
PLS-00103: Encountered the symbol "INST1" when expecting one of the following:

   * & = - + ; < / > at in is mod remainder not rem return
   returning <an exponent (**)> <> or != or ~= >= <= <> and or
   like like2 like4 likec between into using || multiset bulk
   member submultiset
The symbol "* was inserted before "INST1" to continue.


texts: Best way to rank all the columns in a table and store those ranks in another table

texts:
I have SQL Server 2008 and a table w/ hundreds of thousands of records and about 50 columns.

I run a sql statement like:
Select
rank() over(order by col1 desc) as Col1Rank,
rank() over(order by col2 desc) as Col2Rank, etc, etc

I truncate Table_Ranks and then insert all of those ranks for all the columns for caching purposes.  So my select statement had 50 rank() functions.  As my table grows, this operation went from taking a few minutes to a hour, and it keeps getting worse, and affecting the overall performance of that table which has a ton of writes/updates coming in at the same time.

My question is, how can I increase the performance of this operation? Should I select all the rows out of my table and store it in a temp table and do the ranks on the temp table?  Should I offload this to another server somehow?

My concern is that as I add more databases that will do the similar tasks, it will eventually make my server just unresponsive to user requests.

Thanks!


texts: PostgreSQL, get statements of a running transaction

texts:
If I execute a transaction like:

begin;
update test set a=2;
select pg_sleep(9999);

I will get a RowExclusiveLock on test.

If I try to see which query is in that pid pg_sleep will be shown (because it is the currently running query).

Is there any option to get the query that actually create the lock?

Or maybe a query to get all the statements of the running transaction?


texts: Is there a performance hit for querying two different databases?

texts:
I have two tables, A and B, which are located in different databases on the same MySQL server.  I would like to create a derived table C from tables A and B, as an inner join between the two tables.

Something like this (pseudo-code):

create table C as select A.gene, B.pubmed_id, B.pubmed_text from A inner join B ...

Will there be a performance hit in doing so? Would it be more prudent to load tables A and B into the same database? These are both very big tables with millions of records.


texts: Why integer search string is slower on char field?

texts:
I'm using MySQL version 5.1 at my workplace.

I have a column my_cloumn char(10) in my_table.

I recently found that a process was taking longer, and the SQL that was responsible for this was below:

SELECT * FROM my_table WHERE my_column = 12345, which can take some 3 minutes running.

Now, editing and using quotes on the search string this way

SELECT * FROM my_table WHERE my_column = '12345'

retrieves the record in a matter of micro seconds.

I do not have access (direct or indirect) to have the code changed to pass the queries with the quotes (by either direct sql or specifying the correct data type in PDO), and currently may not be able to change the column whilst users are using the database. What I need most is how to explain the difference above.

Why is this faster with quotes and not without?


texts: I have application / service which connects to Oracle database (11g) which shows me "ORA-00942: table or view does not exist" in its log file without any indication of particular  table/view with which it has problem. And there is no way on app side to increase log details to include this data.  I have direct access to the Oracle database which is using by this app (not DBA level privileges, but quite high level of access) any advice on how to check with which table or view my app having issue? Probably I can use some query to check this? I'd like to find out object name so that I can check granted access and any locks on it.


texts: MySQLI stored procedure return PK/AI insertID

texts:
I am using a PHP framework called CodeIgniter for my application. This is using the MySQLI driver for its database work.

This framework has built in support for things like a query builder where I could just code the statement into the model, but I am trying to use a stored procedure setup.

Anyway, I posted some questions with their support and they suggested asking here would be more appropriate.

In my model, I am calling a stored procedure like so:

$data = array("customerID" => 123, "productID" => 456);

...

public function addCustomerToProduct($data){
       $procedure = "CALL addCustomerProduct(?,?)";
       $result = $this->db->query($procedure, $data);
}

As it stands, this works perfectly fine. I send an array of data over, I tell my stored procedure to expect two variables, and I pass them along.

Stored Procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `addCustomerProduct`(IN in_customerID INT, in_productID INT)
BEGIN
    INSERT INTO order_customer_product (customerID, productID, retailAmountAtPurchase, faceValue)
    SELECT
        in_customerID,
        in_productID,
        p.retail,
        p.faceValue
    FROM
        products as p
    WHERE
        p.productID = in_productID;
END

While this works fine, I am not able to get the insert_id from the PK/AI.

The only way this data is passed back (it seems) is when you use :

$this->db->insert('customers', $data);
return $this->db->insert_id();

I am trying to stick with stored procedures as it just makes things easier to debug when its all separate.

How can I return the insert_id to my application when using a stored procedure setup like this?


texts:
I'm trying to tune a relatively simple query:

select bt.col1,bt.col2
from bigtable bt
join smalltable st on bt.smalltable_id = st.smalltable_id
where st.name = 'some name occuring only once in st'
limit 10

The number of matches in bigtable is relatively small compared to the overall size (< 1%)

Here's the explain plan:

+------+-------------+-------+--------+-----------------+---------+---------+-----------------+----------+-------------+
| id   | select_type | table | type   | possible_keys   | key     | key_len | ref             | rows     | Extra       |
+------+-------------+-------+--------+-----------------+---------+---------+-----------------+----------+-------------+
|    1 | PRIMARY     | bt    | ALL    | ix_smalltable_id| NULL    | NULL    | NULL            | 22709766 | Using where |
|    1 | PRIMARY     | st    | eq_ref | PRIMARY,ix_name | PRIMARY | 2       | bt.smalltable_id|        1 | Using where |
+------+-------------+-------+--------+-----------------+---------+---------+-----------------+----------+-------------+

Somehow, even though it would be easier to pickup the index, it doesn't.

I then tried to force the index:

select bt.col1,bt.col2
from bigtable bt force index (ix_smalltable_id)
join smalltable st on bt.smalltable_id = st.smalltable_id
where st.name = 'some name occuring only once in st'
limit 10

But the query plan and query time is the same. It doesn't want to use the index.

I tried doing where bt.smalltable_id in (select ...), but same query plan and time.

But if I fetch the smalltable_id first, and then embed it in the select, it's much faster.

The question

Can I force the index by enabling some flags? Is this a limitation of the query engine?

Table structure

CREATE TABLE `smalltable` (
  `smalltable_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`smalltable_id`),
  KEY `ix_name` (`name`(10))
) ENGINE=InnoDB AUTO_INCREMENT=5698 DEFAULT CHARSET=utf8

CREATE TABLE `bigtable` (
  `bigtable_id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `col1` varchar(255) DEFAULT NULL,
  `col2` varchar(255) DEFAULT NULL,
  ...
  `smalltable_id` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`bigtable_id`),
  KEY `ix_smalltable_id` (`smalltable_id`)
) ENGINE=InnoDB AUTO_INCREMENT=23167374 DEFAULT CHARSET=utf8


texts:
I've never really done anything like this before, but I have a wiki instance setup on my internal network. Since it is a support wiki, in the case that our internal network is inaccessable through our vpn, we want to have the website readily available on a backup external site.

Our external system uses centos and so do our internal systems. So essentially I want to install a wiki instance onto the external system however I am curious how I would go about getting the mysql database connection going between the external site and our internal database.

I guess the question is.... what is the proper way to do this? Our database isn't huge, so some kind of sql cron job that transfers the database to our external box through sftp isn't out of the question.


texts: VarBinary(max) updates very slow on SQL Azure

texts:
We store some documents in a SQL Server database VarBinary(Max) column. Most documents will be a few KB, but sometimes it maybe a couple of MB.

We run into an issue when the file becomes bigger than about 4MB.

When updating the VarBinary column in a on-prem SQL Server, it is very fast (0.6 seconds for a 8MB file).

When doing the same statement on a identical database on SQL Azure, it takes more than 15 seconds!

Also if the code is running from an Azure App Service it is very slow. So it's not our Internet connection that is the problem.

I know storing files in SQL Server is not the preferred way of storing and Blob storage would normally the best solution, but we have special reasons we need to do this, so I want to leave that out of the discussion ;-)

When investigating the execution plans, I see a "Table Spool" taking all the time and I'm not sure why. Below are the execution plans for on prem and Azure.

Identical databases and data. If someone can help, that would be great.

Thanks Chris

On Prem SQL 2016

Azure SQL database


texts: Improve parallel running of bulk copy insert and stored procedures

texts:
I have a Java message listener, which upon receiving a message starts bulk inserting thousands of records into a table.  After that it calls a few stored procedures sequentially that make some updates on the same table.

The problem I run into is that if I have multiple concurrent listeners, then the insert fails with a timeout.  Note that the table has a field upload and stored proc. alter data only for rows with a given upload.  Also I'm using SQLServerBulkCopy from Microsoft's JDBC driver.

Can changing the isolation level in stored procedure resolve the timeout issue? Or is there another way to improve this flow?

DDL

CREATE TABLE test.dbo.airport (
iata varchar(4) NOT NULL,
name varchar(255) NOT NULL,
city varchar(255) NOT NULL,
state varchar(255) NOT NULL,
country varchar(255) NOT NULL,
latitude varchar(255) NOT NULL,
longitude varchar(255) NOT NULL,
upload varchar(255) NOT NULL,
CONSTRAINT PK_iata_upload_name PRIMARY KEY (iata,name,upload)
) GO
CREATE INDEX IDX_city ON test.dbo.airport (city) GO
CREATE INDEX IDX_city_upload ON test.dbo.airport (city,upload) GO
CREATE INDEX IDX_country_upload ON test.dbo.airport (country,upload) GO
CREATE INDEX IDX_upload ON test.dbo.airport (upload) GO;

Java code (called by multiple threads at the same time):

private void upload(Resource resource, String upload) {
    try(var connection = dataSource.getConnection();
        var bulkCopy = new SQLServerBulkCopy(connection);
        var record = new AirportRecord(resource.getInputStream(), upload)) {
        log.info("start upload " + upload);

        SQLServerBulkCopyOptions copyOptions = new SQLServerBulkCopyOptions();
        copyOptions.setBatchSize(batchSize);
        copyOptions.setBulkCopyTimeout(100);

        bulkCopy.setDestinationTableName("airport");
        bulkCopy.setBulkCopyOptions(copyOptions);

        bulkCopy.writeToServer(record);

        log.info("start procs " + upload);
        CallableStatement cStmt = connection.prepareCall("{call updateCountry(?)}");
        cStmt.setString(1, upload);
        cStmt.execute();
    }
}

stack trace:

Exception in thread "pool-1-thread-17" com.microsoft.sqlserver.jdbc.SQLServerException: The query has timed out.
at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.checkForTimeoutException(SQLServerBulkCopy.java:1199)
at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.access$300(SQLServerBulkCopy.java:63)
at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy$1InsertBulk.doExecute(SQLServerBulkCopy.java:715)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7240)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2869)
at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.sendBulkLoadBCP(SQLServerBulkCopy.java:733)
at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.writeToServer(SQLServerBulkCopy.java:1669)


texts: Use @@servername and db_name() on notify operator task

texts:
I'm setting up a maintenance plan and on error I added a "Notify operator task" with some text. But I want to be able to add something like:

  Backup failed on MyDatabase\Instance_name...blablabla

I guess I should use @@SERVERNAME and DB_NAME(db.database_id) someway but so far can't find how to do it, maybe is trivial but no luck find in it.


texts: If you have an 11g or 19c Oracle Standby DB setup using dataguard must you use an RMAN catalog when backing it up? I ask, because it was a requirement in much older versions of Oracle (e.g. 9i) but I can't find confirmation in the documentation of later releases. Many thanks.


texts: How to find the SQL statements that caused tempdb growth?

texts:
The tempdb of a server (SQL Server 2008) increases to 500GB+ several times every month. Is it possible to find out which SQL statements caused this problem? The problem is usually not caused by create table #temp...; insert into #temp... or select ... into #temp... but complex joins.

The Initial Size of some of the tempdb files is also automatically set to much bigger values every time. How to prevent it?

Sometime the cached plans prevent resizing/shrinking the files. How to find which one hold the tempdb?


texts: Disk space full but logical space available in database

texts:
We have a fairly big MS SQL 2008R2 database that resides on a SSD drive. The drive itself only has ~110Gb of space, and the database files are the only files on the drive.

The database is in "Simple" recovery mode, and only has two files, .MDF and .LDF.

The disk is now nearly full: the MDF is currently 109Gb in size. However, SSMS tells me there's nearly 18Gb of "Space Available" (in the 'General' properties page), and if I go through the motions of Shrinking the file it also tells me there's 18Gb of space free. SSMS also tells me the database size is around 132Gb, which surprises me - that wouldn't fit on the drive!

From what I've read, shrink is a really bad idea. However, I'm starting to see replication errors (could not allocate space for object). We've previously tried shrinking the database, but within a few hours the file was back to it's original size.

How should we proceed - given there's apparently 18Gb of free space, should SQL automatically use that free space? Or is it as simple as: we really need more disk space?


texts:
I have these tables:

create table data (
    id int identity not null,
    ref int not null
);

create table reference (
    refid int not null
);

with ~2 million rows in data and ~200k rows in reference. I add ~5k rows to data each day, resulting in 500-5k additional rows in reference per day.

In most of the cases I need to save only one reference for one data column (multiple data entries can have the same reference) but in some cases (currently about 0.1%) I need to save more than one reference.

I probably could use a third table like:

create table data_reference (
     dataid int not null,
     refid int not null
);

to create a n:m mapping but for most of the values it will not have any gain here, it will just create another join...

I could also create a second column in each data row, because in most of the cases I have only one additional reference (but in theory there could be more) and save null if no other reference is there - which seems to me quite bad because there could be tuples with 3 or more references.

Is there an efficient and easy to maintain way to save such data?