texts:
So we have a table that is being used for concurrent access across a number of threads and processes. For certain actions, we want to get an exclusive table lock on the entire table, blocking all access until the action is complete.

Previously, one of our developers did this:

SELECT 1 FROM [TableName] (TABLOCKX);

This worked just fine while our table was small, but as it grew larger became a problem.

After doing some research of this query it seems that it was running once for each row in the table (what SELECT 1 would do, naturally) when really what we wanted was to just select one row, then get our table lock and get out as quickly as possible. I then proposed that we change the query to:

SELECT TOP 1 1 FROM [TableName] (TABLOCKX);

We tested this and it seems to work. The execution plan also recognizes that this will only touch 1 row (whereas the previous one touched all rows).

The question that I have is: Does this work the way that I am thinking? Will this block all access to the table for all operations (including NOLOCK operations). Is there a better way to do this?

Note: The reason that we're doing it this way is that we have a fairly complex .NET library in which we are building some queries on the fly. Because we want them to be modular, we want to make sure that devs don't have to modify or duplicate the queries themselves and can simply note at the beginning of the transaction that they are going to be doing updates to the database. Thereby we can make a single .LockTables(X) which will lock the right tables for the corresponding group.


texts: Measuring Always On Availability Group potential data loss

texts:
I have read these articles few times. I do monitor potential data loss today using some of the methods described in these articles.

Measuring Availability Group synchronization lag by Derik Hammer
Monitor Performance for AlwaysOn Availability Groups
Monitoring Availability Group Replica Synchronization by Jonathan Kehayias

Reading these articles and the description of dynamic management views and performance counters, I do not see any way to measure the exact time for potential data loss if Primary goes down now.
I did inquire about using log sequence number (LSN) values from sys.dm_hadr_database_replica_states. Found out only certain LSN's has a timestamp. Plus, continuously querying the timestamp of those particular LSN is probably not a practical solution.
Do you know how to get the exact time for potential data loss at any given time?
A hypothetical solution will be finding the oldest LSN's timestamp that has not been copied to the secondary.


texts: transactional replication, failing with communication link failure half way through bcp

texts:
I am trying to replicate to a 4th subscriber on a remote site. 3 subscribers work fine locally. the other difference is this is a 2012 subscriber from a 2008 r2 publisher and remote distributor.

When delivering the bcp commands it fails with the following error.

The process could not bulk copy into table '"dbo"."FlightSchedule_tbl"'. (Source: MSSQL_REPL, Error number: MSSQL_REPL0)

I have tried raising the historyverbose level to 2, but this is the only error i get from the agent history.. I have attempted to recreate the snapshot, but this still fails at the same point...

nothing unusual about the table, and its 5 million rows, so not massive amounts of data...

Column_name Type    Computed    Length
FlightId    int no  4
OrigAirport char    no  3
DestAirport char    no  3
DeptDate    datetime    no  8
ArriveDate  datetime    no  8
Airline char    no  10
SupplierId  int no  4
UpdateRef   int no  4
HarvestUpdateKey    int no  4
ActiveFlag  bit no  1

Can anyone help me diagnose this to get a useful error or suggest what the problem might be??  the connection is very slow to this datacentre which is a issue we are attempt to resolve... could this simply be a timeout ???

UPDATE - I configured a seperated publication with just this table in and set the verbose logging to 2. I then get

Error messages:
The process could not bulk copy into table '"dbo"."FlightSchedule_tbl"'. (Source:   MSSQL_REPL, Error number: MSSQL_REPL0)

An existing connection was forcibly closed by the remote host.
Unspecified error
Failed to send batch after max errors
TCP Provider: An existing connection was forcibly closed by the remote host. (Source: MSSQLServer, Error number: 10054)

Communication link failure (Source: MSSQLServer, Error number: 10054)

so looks like the network between datacentres...  strange that it would always die at the same point, would there be a reason for this ?

The error log on the target server
  Error: 4014, Severity: 20, State: 13
  A fatal error occurred while reading the input stream from the network. The session will be terminated (input error: 64, output error: 0).


texts: Why is performance non-linear if I do many UPDATEs in a single transaction

texts:
An older question covers some reasons why the performance of multiple INSERTS within a single transaction is non-linear as the INSERT count grows.

Following some of the advice there, I've been trying to optimise running many UPDATEs in a single transaction. In the real scenario, we're batch processing data from another system, but I've got a smaller scenario for testing.

Given this table on postgresql 9.5.1:

\d+ foo
                                         Table "public.foo"
 Column |  Type   |                    Modifiers                     | Storage | Stats target | Description
--------+---------+--------------------------------------------------+---------+--------------+-------------
 id     | bigint  | not null default nextval('foo_id_seq'::regclass) | plain   |              |
 count  | integer | not null                                         | plain   |              |

I have the following test files: 100.sql, 1000.sql, 10000.sql, 50000.sql and 100000.sql. Each contains the following lines, with UPDATE repeated according to the filename:

BEGIN;
UPDATE foo SET count=count+1 WHERE id=1;
...
UPDATE foo SET count=count+1 WHERE id=1;
COMMIT;

When I benchmark loading each file, the results look like this:

              user     system      total        real   ms/update
100       0.000000   0.010000   0.040000 (  0.044277)  0.44277
1000      0.000000   0.000000   0.040000 (  0.097175)  0.09717
10000     0.020000   0.020000   0.230000 (  1.717170)  0.17171
50000     0.160000   0.130000   1.840000 ( 30.991350)  0.61982
100000    0.440000   0.380000   5.320000 (149.199524)  1.49199

The average time per UPDATE increases as the transaction includes more rows, suggesting that the performance is non-linear.

The earlier question I linked to suggests that indexes can be an issue, however this table has no indexes and a single row.

Is this just a case of "that's how it works", or are there some settings I can tune to improve the situation?

Update

Based on a theory in the current answer, I've run an additional test. The table structure is the same, but the UPDATEs all change different rows. The input files now look like this:

BEGIN;
UPDATE foo SET count=count+1 WHERE id=1;
UPDATE foo SET count=count+1 WHERE id=2;
...
UPDATE foo SET count=count+1 WHERE id=n;
COMMIT;

When I benchmark loading these files, the results look like this:

              user     system      total        real   ms/update
100       0.000000   0.000000   0.030000 (  0.044876)  0.44876
1000      0.010000   0.000000   0.050000 (  0.102998)  0.10299
10000     0.000000   0.040000   0.140000 (  0.666050)  0.06660
50000     0.070000   0.140000   0.550000 (  3.150734)  0.06301
100000    0.130000   0.280000   1.110000 (  6.458655)  0.06458

From 10,000 UPDATEs and up (once setup costs are amortised) the performance is linear.


texts: select does not use index when using different column


texts:
My problem is that I need to digitize a series of 5 ticksheets/surveys.

Each one has a generic structure but are used at different times and have different questions; each one has at least 50-75 questions of varying data types. Each question also has a category which is relevant to an outside system that this information will be ported to (moving the information is outside the scope of this project).

I would like to have it so I don't need 1 table for each form with 50-ish columns.

As of now I have a design which I believe is the proper way to do it; but I have concerns about performance and would like to know if this is best way to do it in case they wish to add more surveys.

My DB diagram is as follows, using SQL Server 2012.

Missing junction table between Question and SurveyType deciding which questions will be displayed on each form

The answer's type in SurveyQuestions will be enforced in the App(unless there's a better way in the DB).

My main concern with this approach is that SurveyQuestion will become too large by sheer volume of rows.  Any input or other approaches would be greatly appreciated.

There is also another option I just put together; by separating out the survey types in base types I think it will take away some of the potential issue with performance.

2:


texts: Dealing with null values in non-nullable columns in a NoSQL database

texts:
If I have an existing table in a NoSQL database cluster and I want to add a new column that should never return null, how do I handle it in my application code?

I can update all exist records and set that new column to some default value (if there is a sensible default).
I can have my application code assume a default value if it reads a records with no value for the field
???

The problem I see with the first solution is that there may be existing code that writes records without the mandatory column leaving the table with "broken" records. The second solution may "mask" existing code that fails to write the new column value.

How do people handle this scenario?


texts: MongoDB presplitting chunks for compound shard key

texts:
In my mongodb setup I have a compound shard key {"region" : 1, "foo" : 1, "bar" : 1} and I know the values region can be and that each region should be on one chunk.

Therefore I'd like to pre-split based on the region key only. The sharding status should look like that afterwards:

{ "region": { "$minKey" : 1 }, "foo": { "$minKey" : 1 }, "bar": { "$minKey" : 1 } } -> { "region": region1, "foo": { "$minKey" : 1 }, "bar": { "$minKey" : 1 } } on: shard1
{ "region": region1, "foo": { "$minKey" : 1 }, "bar": { "$minKey" : 1 } } -> { "region": region2, "foo": { "$minKey" : 1 }, "bar": { "$minKey" : 1 } } on: shard2
{ "region": region2, "foo": { "$minKey" : 1 }, "bar": { "$minKey" : 1 } } -> { "region": { "$maxKey" : 1 }, "foo": { "$minKey" : 1 }, "bar": { "$minKey" : 1 } } on: shard3

I've tried some ways to achieve that, but nothing worked:

db.runCommand( { split : "mydb.mycollection" , middle : { "region" : "region1" } } ); will return an error, because the complete shard key have to be part of the split.
db.runCommand( { split : "mydb.mycollection" , middle : { "region" : "region1", "foo" : { "$minKey" : 1 }, "bar" : { "$minKey" : 1 } } } ); and db.runCommand( { split : "mydb.mycollection" , middle : { "region" : "region1", "foo" : "$minKey" , "bar" : "$minKey" } } ); will interpret the minKey as String and split on that base which is wrong.

How can I finally split chunks with a compound shard key on a single field base?!

Cheers.


texts: How to config Enterprisedb work with PostgresXC ?

texts:
Util now, I created a cluster (PostgreSQL database + Postgres-XC) with 5 server: 1 GTM, 1 Coordinator, 3 Data Nodes.

I intend to create a Enterprisedb (Postgres Plus Advanced Server, link: EDB's home ) databases cluster using PostgreXC. But I don't know that EDB can work with PostgreXC or not ?

Cross my post: EDB's Forum


texts: Powershell - SQL Server - connectionstring in loop for multiple Instances

texts:
Thisis part of a bigger script which finds DatabaseFiles on a SQL Server machine (multiple instances).

Following should just return all files for 3 instances.

The server is called V3000801 and there is one default instance + 2 named instances on there. It's ok with me if either default or named doesn't work I'll work around this alone (most likely create a flag and do default with another connection string).

$SqlCmd.ExecuteNonQuery() just returns -1 which does not make any sense for me.

Thanks for the help

for($i=0;$i -lt $instances.Length;$i++){
  $SqlConnection = New-Object System.Data.SqlClient.SqlConnection;
  $Server= "V3000801\"+$instances[$i];
  $SqlConnection.ConnectionString = "Server = $server ; Database = master; Integrated Security = sspi;trusted_connection=true";
  $sqlQuery="SELECT physical_name FROM sys.master_files;";

  Write-Host $SqlConnection.ConnectionString;

  $SqlConnection.Open();
  $SqlCmd = New-Object System.Data.SqlClient.SqlCommand;
  $SqlCmd.CommandText = $sqlQuery;
  $SqlCmd.Connection = $SqlConnection;
  $SqlCmd.ExecuteNonQuery();
  $SqlConnection.Close();
}


texts: What determines whether `pg_dump` includes a varchar length?

texts:
I work on a project where we regularly dump our Postgres database schema and commit it in the project repository. However, depending on which developer dumps the schema (via pg_dump), varchar columns may be given without a length:

email character varying NOT NULL

or with a length:

email character varying(255) NOT NULL

Is there a configuration option that determines whether the length is included in the schema dump?


texts: Append to SQL from Access is dropping leading zeros

texts:
My append query from Access to SQL is dropping leading zeros.

The "Number" is stored in an Access table with the field type of Text.  In Access the characters appear as an 8 character field (00012345).

The "Number" is being appended to SQL into a table with a field type of Char (8).

When I open the SQL table the value showing is 12345.  The leading zeros have been dropped.

Can anyone help me understand why the leading zeros are being dropped and what I can do to have the data append with the leading zeros intact.


texts: MySQL Trigger Query Not Deleting As Expected

texts:
I have a test database as shown below. If date is updated in tbl1, I would like for a trigger to fire that removes all entries from tbl2 where tbl1.host = tbl2.host2 AND tbl1.date = NEW.date.

This is as close as I can get but it doesn't work. Thank you kindly in advance!

DELETE tbl2.* FROM tbl1,tbl2
WHERE (tbl1.host = tbl2.host
AND date = NEW.date)

SAMPLE DATA

CREATE TABLE IF NOT EXISTS tbl1
(
  host varchar(10) NOT NULL,
  package1 varchar(10) NOT NULL,
  date datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DELIMITER $$
CREATE TRIGGER `Before_Insert` BEFORE INSERT ON `tbl1`
FOR EACH ROW DELETE tbl2.* FROM tbl1,tbl2
WHERE (tbl1.host = tbl2.host AND date = NEW.date)
-- Should be tbl2.host2 (no host field in tbl2) EDIT.
$$
DELIMITER ;

CREATE TABLE IF NOT EXISTS `tbl2` (
  `packageid` tinyint(10) NOT NULL,
  `package2` varchar(10) NOT NULL,
  `host2` varchar(10) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

INSERT INTO `tbl2` (`packageid`, `package2`, `host2`) VALUES
(1, 'a', '1'),
(2, 'b', '2'),
(3, 'c', '3'),
(4, 'd', '4');

ALTER TABLE `tbl1`
  ADD PRIMARY KEY (`host`,`package1`) USING BTREE;

ALTER TABLE `tbl2`
  ADD PRIMARY KEY (`packageid`);

ALTER TABLE `tbl2`
  MODIFY `packageid` tinyint(10) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=5;


texts: Do you prefer to store confirm password property in database?

texts:
I am designing a database and it has came to my mind to check entered password (for register page) with confirmed password in database as well as in programming layer.
Do you recommend it?
Any comments would be appreciated.


texts: Database transaction consistency on PostgreSQL

texts:
In a table with three columns (serial id, varchar name, varchar email) and having two different transactions executing concurrently as the ones bellow:

Transaction 1:

BEGIN;
INSERT INTO public.users (name, email) VALUES ('tart', 'tart@warm.com');
INSERT INTO public.users (name, email) VALUES ('tart123', 'tart123@warm123.com');
COMMIT;

Transaction2:

BEGIN;
INSERT INTO public.users (name, email) VALUES ('raspy', 'raspy@sun.com');
INSERT INTO public.users (name, email) VALUES ('raspy123', 'raspy123@sun123.com');
COMMIT;

Is it possible that the outcome becomes the following?

1 | tart     | tart@warm.com
2 | raspy    | raspy@sun.com
3 | tart123  | tart123@warm123.com
4 | raspy123 | raspy123@sun123.com


texts: Query to list "last backup time" listing wrong times ( SQL Server 2008 R2 )

texts:
I use this query to list last backup dates of our databases:

SELECT SDB.NAME AS DATABASENAME,
COALESCE(CONVERT(VARCHAR(12), MAX(BUS.BACKUP_FINISH_DATE), 101),'-') AS LASTBACKUPTIME
FROM SYS.SYSDATABASES SDB
LEFT OUTER JOIN MSDB.DBO.BACKUPSET BUS ON BUS.DATABASE_NAME=SDB.NAME
GROUP BY SDB.NAME
    ORDER BY 2 ASC

But today, it shows me "09/15/2015" for all databases, but we had a problem in the cluster, so the backup job didn't run. Why is this query listing "09/15/2015' if there are no backups for today?

last time the job run was 09/14/2015.

Edit 1:

With James Anderson's query, I got this too ( filtering with bckset.[type] ='D)

Again, I don't have backups from 2015-09-15, only 2015-09-14.

EDIT 2:

With some tests, I see that these queries shows me last backup date, even if I only have log backups. Is there a way to list "last full backup" date??


texts:
I want to benchmark some queries on an oracle System from a JDBC application.

However, the database seems to cache at some point in time. However, this is not intended in this case.

Is there anything like the MySQL query hint SQL_NO_CACHE.

There are Oracle query hintes /*+ FULL(test) */ and there is even the CACHE hint, but they concern whether the query results goes to the cache at all ...

What can I do on an Oracle System to get rid of any caching?


texts: Is it a good practice to apply Vertical Partition to a newly created model?

texts:
Suppose you are modeling the ER model for a new Object Oriented Web Application.

There is a table with 20 columns, and there are going to be a lot of INSERTs populating 10 columns, and a lot of UPDATEs populating the remaning columns.

The amount of INSERT and UPDATE statements on this table may reach thounds per second.

Since there are a lot of locks occuring all the time (caused by the INSERTs and UPDATEs), there is a concern that performance may be a problem, since response time has to be a reasonable one for a Web Application.

Someone suggests that the table should be split into 2 other tables: One containing the columns that are inserted first, and the other one contaning the remaning columns, that would be updated.

Keep in mind that this would require a more complex Object-relational mapping, since the OO Model and all the documents consider it to be 1 single entity, not 2.

My questions:

Is it a good suggestion? Would you consider it? Is there a better way of doing it? is it going to help at all?

or

Is this manly a hardware problem? Should I model my application database ignoring this kind of optimization and focusing on getting the entities right? Should I just tell the hardware guys that I need better I/O?

Please let me know if any information is missing and thanks in advance.


texts:
What steps would you do to be sure that STANDARD edition with its 128GB RAM limit is enough for your production?

My subjective feeling is that we are somehow on the edge and I am looking for strong arguments to do the right decision to the future.

We are now running EVALUATION edition (so in fact ENTERPRISE). That gave me the space for testing the influence of having RAM limit (Max Server Memory) set to 128 GB and more (in fact set to 169 GB). Let's call the states before (Max Server Memory = 128 GB) and after (Max Server Memory = 169 GB). While testing I was collecting wait stats and several Memory based counters.

In general, wait statistics didn't change much while monitoring before and after the RAM increase. Not even in buffer latch and memory category of wait types.

Memory counters provides more interesting values but are they strong enough indicators of lack of memory?

Memory Grant Pending: value stays = 0 before and after

Total & Target server memory: keeps close to each other before and after - no spikes

System memory state:
before - it's mostly in "Physical memory state us steady" state
after - it goes to "Available physical memory is hight" state, not suprisingly

Page life expectancy: varies much over time (before and after)
before: sometime goes under 300 but in average it is more
after: sometimes goes under 300 (but less time)

Batch Reguests/sec & SQL Compilations / sec: the ratio between those two counters didn't change (before / after) - so there seems to be enought space for plans in the buffer, unfortunately the ratio is still higher than 10%.


texts: What is the benefit of having multiple databases in the same Availability Group vs single AG per database

texts:
We are planning a migration from current mirroring architecture to Availability Groups for several databases. For every Database we have defined the primary/secondary replica configuration.

The question that I'm unsure is: do we need to group the databases with the same primary/secondary configuration into the same AG, or use one AG per Database. What are pros&cons of having individual AGs vs AG with multiple databases? Is there a best practice to determine if a database needs its own AG or can be combined with others? Any performance or failover advantages?


texts: Is there a way of implementing a composite primary key with varying fields in MongoDB?

texts:
Here is the information about my development environment:

-Mongodb 3.0.0

-Microsoft Visual Studio Professional 2013

-.NET Framework 4.5

We are trying to design some MongoDB collections at work that are associated with each in a complex way.

Speed performance is very important because we will deal with millions of records.

In any case, here is a business requirement.

Let's say I have health care policies that vary across provincial states in the United States.

Along, with concern such as provincial states in the US, I may need to consider certain exceptional cases such as whether the person is a senior citizen, sex, health history, chronic illnesses, etc.

The Exceptional cases really vary in their values.

Let's say we have a Person Model.

Person

_id: ObjectId

FirstName: String

LastName: String

ProvincialStateOfResidenceID: String

ExceptionCasesDocRefs: Array[ ExceptionCasesID ]

The ProvincialStateOfResidence field will always have a value.

The ExceptionCasesDocRefs array could be empty Or It could contain multiple IDs

Let's have a HealthPolicy Model that will always have a value for ProvincialStateOfResidenceID field.

HealthPolicy

ProvincialStateOfResidenceID: String

ExceptionCasesDocRefs: Array[ ExceptionCasesID ]

PolicyCost:  Double

PolicyDuration: Double

RequiredHealthEvaluation: Double

blah.blah.....

blah......

We also have an ExceptionCasesReference Collection that will contain values like the following:

ExceptionCasesReference

ExceptionCasesID : ObjectID

ExceptionCase: Name

ExceptionCaseDescription: String

I need to somehow make the following into a composite primary key:

ProvincialStateOfResidenceID: String

ExceptionCasesDocRefs: Array[ ExceptionCasesID ]

It is problematic to make the above into a composite primary key because ExceptionCasesDocRefs can have values that range from NULL to a many ExceptionCasesID.

Is there a way of modelling the aforementioned MongoDB Collection(s) in such a way that speed performance is upheld?


texts: SQL error: Unknown column in 'where clause'

texts:
I have following very simple PHP MySQL Query:

$sql = 'SELECT * FROM bundesland WHERE bkuerzel = ' . $_GET['q'];
$result = mysql_query($sql, $con);

When I execute the query with a click on a item with the value "Wien" on a dropdown menu I get "SQL error:Unknown column 'Wien' in 'where clause' in", but as you can see in the picture below I have the column.


texts: What does remote database have to do for DB link in Oracle

texts:
In local database, it has to create DB LINK through CREATE DATABASE LINK.

What about remote database side, what does it have to do?
On fixed user link, local database need to know ID and password set.
Is the ID is a usual account with which someone can access to the remote database without DB LINK or the ID is only for DB LINK?


texts:
I'm trying to optimize a ~100GB database. All data values are in one single innodb table, is this bad? Here's what a common query looks like:

SELECT `CensorCode`, `LocalDateTime`, `UTCOffset`, `DateTimeUTC`, `MethodID`, `SourceID`, `QualityControlLevelID`, `DataValue`, `OffsetTypeID`, `OffsetValue`, `d`.`SampleID`, `s`.`LabSampleCode`, `d`.`QualifierID`, `q`.`QualifierCode`
FROM (`datavalues` d)
LEFT JOIN `samples` s ON `d`.`SampleID` = `s`.`SampleID`
LEFT JOIN `qualifiers` q ON `d`.`QualifierID` = `q`.`QualifierID`
WHERE `d`.`SiteID` =  '15'
AND `d`.`VariableID` =  '8'
AND `d`.`LocalDateTime` >= '2009-04-18T00:02'
AND `d`.`LocalDateTime` <= '2009-04-20T00:02'

Here's what the explain statement on the above query looks like:

| id | select_type | table | type        | possible_keys                               | key                                         | key_len | ref               | rows    | Extra                                                                     |
+----+-------------+-------+-------------+---------------------------------------------+---------------------------------------------+---------+-------------------+---------+---------------------------------------------------------------------------+
|  1 | SIMPLE      | d     | index_merge | FK_DataValues_Variables,FK_DataValues_Sites | FK_DataValues_Sites,FK_DataValues_Variables | 4,4     | NULL              | 1390435 | Using intersect(FK_DataValues_Sites,FK_DataValues_Variables); Using where |
|  1 | SIMPLE      | s     | eq_ref      | PRIMARY                                     | PRIMARY                                     | 4       | odm.d.SampleID    |       1 |                                                                           |
|  1 | SIMPLE      | q     | eq_ref      | PRIMARY                                     | PRIMARY                                     | 4       | odm.d.QualifierID |       1 |

Does this mean it's using indexes? If not, could someone explain how to create an index, I don't understand what it means. Any other suggestions would be greatly appreciated. Thanks in advance.

Also, here is the output from SHOW CREATE TABLE

| datavalues | CREATE TABLE `datavalues` (
  `ValueID` int(11) NOT NULL AUTO_INCREMENT,
  `DataValue` double NOT NULL,
  `ValueAccuracy` double DEFAULT NULL,
  `LocalDateTime` datetime NOT NULL,
  `UTCOffset` double NOT NULL,
  `DateTimeUTC` datetime NOT NULL,
  `SiteID` int(11) NOT NULL,
  `VariableID` int(11) NOT NULL,
  `OffsetValue` double DEFAULT NULL,
  `OffsetTypeID` int(11) DEFAULT NULL,
  `CensorCode` varchar(50) NOT NULL DEFAULT 'nc',
  `QualifierID` int(11) DEFAULT NULL,
  `MethodID` int(11) NOT NULL DEFAULT '0',
  `SourceID` int(11) NOT NULL,
  `SampleID` int(11) DEFAULT NULL,
  `DerivedFromID` int(11) DEFAULT NULL,
  `QualityControlLevelID` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ValueID`),
  UNIQUE KEY `DataValues_UNIQUE_DataValues` (`DataValue`,`ValueAccuracy`,`LocalDateTime`,`UTCOffset`,`DateTimeUTC`,`SiteID`,`VariableID`,`OffsetValue`,`OffsetTypeID`,`CensorCode`,`QualifierID`,`MethodID`,`SourceID`,`SampleID`,`DerivedFromID`,`QualityControlLevelID`),
  KEY `FK_DataValues_Sources` (`SourceID`),
  KEY `FK_DataValues_QualityControlLevels` (`QualityControlLevelID`),
  KEY `FK_DataValues_OffsetTypes` (`OffsetTypeID`),
  KEY `FK_DataValues_CensorCodeCV` (`CensorCode`),
  KEY `FK_DataValues_Variables` (`VariableID`),
  KEY `FK_DataValues_Methods` (`MethodID`),
  KEY `FK_DataValues_Qualifiers` (`QualifierID`),
  KEY `FK_DataValues_Samples` (`SampleID`),
  KEY `FK_DataValues_Sites` (`SiteID`)
) ENGINE=InnoDB AUTO_INCREMENT=282507112 DEFAULT CHARSET=utf8 |


texts: SSAS Cube Process Manually works but MDX query doesnt work

texts:
I am new to SSAS.

Here's a cube which is rebuilt by a SSIS package everyday but it seems to cause duplication. We manually 'Process' the cube using the GUI, it then updates this table with the correct date and time and the data is correct after we do this:

SELECT CUBE_NAME, LAST_DATA_UPDATE FROM $System.MDSCHEMA_CUBES

However, we have been trying to automate the 'Process' at cube level and have added this code to a SQL Agent Job which runs as type = SQL Server Analysis Services Command and as the SQL Server Agent Service Account:

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Parallel>
    <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400" xmlns:ddl500="http://schemas.microsoft.com/analysisservices/2013/engine/500" xmlns:ddl500_500="http://schemas.microsoft.com/analysisservices/2013/engine/500/500">
      <Object>
        <DatabaseID>DBNAME</DatabaseID>
        <CubeID>CUBENAME</CubeID>
      </Object>
      <Type>ProcessFull</Type>
      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
    </Process>
  </Parallel>
</Batch>

This does NOT fix the cube data and also doesn't update the data in table:

SELECT CUBE_NAME, LAST_DATA_UPDATE FROM $System.MDSCHEMA_CUBES

Is there anything else I can check or do to get the MDX query to work?
I've also used the same code in an SSMS window to process the dimensions, then the measure group and then the ProcessFull on the Cube. To no avail.

Any help will be appreciated.


texts: When were there changes in the memory settings of SQL Server

texts: Is there a log, query or any other way to see when there was a change made to the memory settings of SQL Server?


texts: existing foreign key that references fields with different datatype (char(8) and varchar(8))

texts:
This is more a quest for information that a request for help...

I'm upgrading some old databases (running on sql 2005 and compatibility level 70, I suspect they are online since sql 6.5) to get rid of a couple of old servers and deploy SQL Server 2012 or 2014.

I had to deal with some sql collation now unsupported so I dropped some indexes and foreign keys. While trying to restore them I've been stopped by this error:

  Msg 1778, Level 16, State 0, Line 2 Column 'dbo.Table1.IDColumn' is
  not the same data type as referencing column
  'Table2.ColumnFK' in foreign key
  'Foreign_key'.

The first field is declared as varchar(8) and the second is a char(8) so the message is correct.

I'm doing all the tests on a copy so I double checked on the production database and the foreign key is there, linking a char field with a varchar field.

When scripting the constraint this is what I got (that's the script that is giving error):

ALTER TABLE [dbo].[Table2]
WITH NOCHECK ADD  CONSTRAINT [Foreign_key]
FOREIGN KEY([ColumnFK]) REFERENCES [dbo].[Table1] ([IDColumn])
GO

ALTER TABLE [dbo].[Table2]
NOCHECK CONSTRAINT [Foreign_key]
GO

I found an answer on SO stating that it is not possible to have a foreign key using 2 columns that have different datatype but a workaround is supplied so the issue is not critical.

The question is: was it ever allowed to create a relationship on fields with different data types without tricks? Was there some sort of 'compatibility' between char and varchar?

The answer should be a obvious 'yes, it is possible' but I can't find any evidence stating that this behavior is (was?) allowed and/or expected: I have the foreign key in place but it should not be possible to create it.


texts: Is dropping foreign key constriants an event in Oracle?

texts:
I wrote the following trigger in Oracle:

create or replace trigger foreign_drop before ddl on database
begin
  insert into ddl_log
  select ora_sysevent, ora_dict_obj_owner, ora_dict_obj_name, null, user,
sysdate
  from dual;
end foreign_drop;

It fires whenever a DDL event happens. But when I drop a foreign key constraint, nothing is recorded. Is dropping a foreign key constraints considered an event? How could I record the action of dropping a foreign key constraint?


texts:
PostGIS provides many different methods of address standardization

Some newer methods return stdaddr,

standardize_address (which requires explicit lex, gaz, and rules)

Others return norm_addy

pagc_normalize_address (which provides default pagc_lex, pagc_gaz, and pagc_rules) to standardize_address
normalize_address (which is the original)

However, geocode() only takes norm_addy, how do I cast a stdaddr to norm_addy. I intend to cache my stdaddr on a table and I'd like to have a quick and fast way to finish the geocode.


texts: Searching all database schemas - how to use wildcards?

texts:
Using sqlworkBench I'm looking for column names. I'd like to use wild cards in my search using the below query. when I search for column "activated_at" it works but searching with wild card fails, can anyone help ?

SELECT
    table_schema,
    table_name,
    column_name,
    data_type,
    ordinal_position

FROM  INFORMATION_SCHEMA.COLUMNS

WHERE column_name = 'activated_a*' ;


texts: What process would you use to determine references on a database that has no integrity?

texts:
Imagine you inherit a database which has a bunch of tables, no documentation and zero referential integrity. Now you're trying to find out what are the various relationships between different tables.

What process would you go through to do this e.g. finding common data patterns, matching these across tables?


texts: SQL beginner, need help with WHERE syntax error

texts:
I am having a problem with a sql query that is supposed to find authors that have published at least one book priced $13 or more. IT is supposed to display first and last name, sorted by last name as long as the author has published at least one book priced $13 or more. Below is my query so far:

SELECT authors.au_fname, authors.au_lname, titles.price
FROM authors, titles  ORDER BY au_lname ASC
WHERE titles.price > "13";

The first picture shows the db's first table authors and the second shows titles, which holds the price column.

What am I doing wrong? I cannot figure out what is wrong with my syntax.

Thanks!


texts:
I need to optimize some queries with several joins. I plan to create some indexes for these queries.

I am currently using MariaDB with TokuDB storage engine.

Should I expect the indexes for the join ?


texts:
Is LDAP authentication and authorization in MySQL possible? What are the requirements (MySQL version, third-party software etc.)? How it's organized?

Thanks.


texts: TempDB Contention with 24 physical tempdb files on 48 core AMD Opteron box

texts:
I just received the following notification about an instance of SQL Server 2005.  The instance has a core-to-tempdb-file ratio of 2:1 for a total tempdb file count of 24 files.  Contention should not be happening - how would I detect the source of this contention?  TempDB and all other databases are on SAN storage via 10 GB Ethernet.  The SAN has 46 10k SAS drives configured in one RAID-60 array.  This array is shared with several VMWare servers and an Exchange server or two.

Notification from Idera SQL Diagnostic Manager:

11/8/2012 10:49:00 PM, Tempdb Contention (ms) on MGSQL01 is Critical.

Tempdb latch contention has been detected on MGSQL01. The total wait time detected is 1782 milliseconds. This is an indication that performance is being impacted by contention on allocation maps in tempdb. If this is a regular problem it may be alleviated by following best practices with respect to tempdb file count, size, and IO subsystem.

PFS Wait Time: 1782 ms GAM Wait Time: 0 ms SGAM Wait Time: 0 ms

Tempdb Contention (ms): Current wait time for tempdb allocation maps (GAM, SGAM, and PFS), in milliseconds. This alert may only be raised on instances running SQL 2005 or above.


texts: In SQL Server, why can a backward scan of clustered index cannot not use parallelism?

texts:
I've been reading about SQL Server internals and every book or blog mentions this about backward scans.

  A backward scan of a clustered index cannot use parallelism

The only post that said something is this one below. The post says that the SQL Server team did not implement the required optimizations for a backward scan. https://www.itprotoday.com/sql-server/descending-indexes

Since the leaf level pages are linked using a doubly linked list, I do not understand why a backward scan is different to a forward scan. Any clarification is really appreciated.


texts: merge back to back rows based on start & end date


texts: MongoDB Atlas M0 (Free Tier) BI capabilities

texts:
I'm using MongoDB in my server and developed BI tool using cube-js & mongosqld, which worked when mongo was running locally, however after I moved to Atlas using M0 tier I could not connect to it anymore using mongosqld, this is the error I get:

  unable to load MongoDB information: failed to create admin session for loading server cluster information: unable to execute command: server selection error: server selection timeout

Checking the documentation again I realized BI Connector is not supported in M0:

https://docs.atlas.mongodb.com/bi-connection/#bi-connection

So I guess this error is expected ?

In that case, what other options I have to develop a visualisation tool while keep using M0 tier ? Of course the preferred way for me is to keep using cube-js app which I already developed.

This is the documentation I used originally and it does mention limitation for free tier but doesn't say it's not supported at all so I'm confused what's the root cause for this error message and if it should work or not:

https://docs.mongodb.com/bi-connector/v2.1/tutorial/connecting-to-atlas/

I also verified I CAN connect using mongo:

mongo mongodb+srv://<my-user>:<my-password>@<my-domain>/<my-db>

however when I try the same uri in mongosqld:

mongosqld --mongo-ssl --sslMode allowSSL --auth --sslPEMKeyFile test.pem --schema <my-schema>.drdl --mongo-uri mongodb+srv://<my-user>:<my-password>@<my-domain>/<my-db>

I get the following error:

  when authentication is enabled, admin credentials must be provided with --mongo-username and --mongo-password or in a config file at 'mongodb.net.auth'

I then tried to connect using the following command:

mongosqld --mongo-ssl --sslMode allowSSL --auth --sslPEMKeyFile test.pem --schema <my-schema>.drdl --mongo-uri mongodb+srv://<my-domain>/<my-db> --mongo-username <my-yser> --mongo-password <my-password>

and got this error:

  error parsing uri: authsource without username is invalid

then I tried without "srv":

mongosqld --mongo-ssl --sslMode allowSSL --auth --sslPEMKeyFile test.pem --schema <my-schema>.drdl --mongo-uri mongodb://<my-domain>/<my-db> --mongo-username <my-yser> --mongo-password <my-password>

and got the error mentioned originally - "failed to create admin session"


texts: LOG all SQL Server queries with their durations

texts:
Is there a way to log all Microsoft SQL Server queries executed on a certain server, with their respective durations?

I'd like to be able to analyze all queries will a tool like what PGBADGER does for PostgreSQL. While we're at it: do you know of any such tools?


texts:
We currently use CDC to replicated our Production DB offsite (Microsoft SQL Server 2008 R2), but using CDC and the way we process the files is very unstable. Also note that the two servers cannot communicate with each other due to security. The CDC files gets transferred through various channels to get to the offsite server. The file transfer process has not let us down in more that 3 year now so that is not the issue.

I am busy with a project to change the offsite replication to log shipping. I have setup log shipping on production and the files arrive safely on the offsite server. However I am struggling to setup a job that automatically restores the log files that gets transferred to the offsite server. Normally this would not be an issue to setup if the two server could communicate with each other.

I also need to setup the standard job to send warnings if no files are being received.

Please help me on the right path.


texts: Trigger function accessing newly updated data from another table

texts:
I have two tables A and B.
In one transaction I update both tables. In the trigger function after update I want to select data from both tables, but the result is the old data. The trigger is on table A, but they both are being updated in the same transaction.
Is this possible to achieve or I have to think for another approach?


texts: Oracle: How to get list of objects accessible to the user?

texts:
I want to get list of all objects which is accessible to the login user. The list should include Table, Sequence, Function, Procedure, View, Sequence, Index, Trigger etc.

I am using Oracle 11g and Oracle 12c. My requirement is that the owner of the db objects grant privileges to other user, so that user can access those objects by running select or other query. I did try earlier dba_objects but DBA does not allow to grant permission for dba_objects to the user. So I am looking for different solution.

I did try all_objects that list of all objects accessible to the user, but It lists the Table, View, Index and Sequence only.

Either I am missing something or its returns it only.

Can anybody suggest me correct way to get all accessible objects to the user?


texts: Huge Size (still) with Differential Backup

texts:
I am new to differential backup so please bear with me.

I have a database which the full backup size is ~55GB.
I perform ETL nightly on this database. The ETL process involves UPDATE, DELETE, and INDEX DISABLE/REBUILD operations.
1st differential backup produced 48GB backup file!

Now, I know that this is most likely (if not 100%) caused by the index rebuilds (and they are on big tables too!).
As index rebuilding is a pretty common technique used during ETL process, what option(s) do I have to minimise the size of differential backup on a data warehouse database?


texts: what are the implications of setting up a subscriber as a publisher?

texts:
We have application database (APP_DB) that creates a "live copy" of itself via transactional replication (push) to a database (COPY_DB) on a separate server.  This COPY_DB is used for reporting and troubleshooting.

There is now a need for a small subset of the tables (4 tables out of 150) to be placed ("live") in a second database (NEW_DB).  What would be the implications of setting up COPY_DB as a publisher of these four tables to subscriber NEW_DB?  Good idea?  Horrible idea?

It seems like a silly idea to me, but I find myself in a situation where I have more control over the server where COPY_DB and NEW_DB live and could more easily implement such a solution rather than setting up a second publication on APP_DB.


texts: select query not returning expected result

texts:
I have a table which is having 30 columns. And I have executed the below query (using postgres 9.1, executed via pgAdmin)

select column2 || '|' || column3 || '|' || column4
from tablename
order by column1 asc

I am getting 5 rows with empty result. and I noticed that column3 having values as 'Data1|Data2' while I execute select * from table name.

Any idea?

Table structure is simple. All are character varying except column1 (numeric) and data having simple texts except column3. Column3 having data with delimer ("|). i.e., 12345|23333

How can I achieve the same in this?

psql -d mydb-U user1 -c "\copy tablename to
    '/home/user/table_data.csv' delimiter '|' csv header";


texts:
Background information:

I have a table containing upload information, every day I get more and more uploads but most of the queries I run center around information gathered within the last 12 months; frequently it uses an even tighter time scale and queries are restricted to information gathered in the last 30 days

This system has been in existence from 2004 when there were 400 uploads, today there are 2+ million

Table structure:

CREATE TABLE `data_mediagallery` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `status` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `contenttype` char(40) NOT NULL DEFAULT '',
  `filename` varchar(100) NOT NULL DEFAULT '',
  `injector` char(40) NOT NULL DEFAULT '',
  `hits` int(11) DEFAULT '0',
  `message` longtext,
  `date` datetime DEFAULT NULL,
  `title` varchar(80) NOT NULL DEFAULT '',
  `tags` varchar(255) NOT NULL DEFAULT '',
  `metadata` blob,
  `location` char(8) NOT NULL DEFAULT '',
  `uid` int(11) unsigned NOT NULL DEFAULT '0',
  `filesize` bigint(20) NOT NULL DEFAULT '0',
  `upload` datetime DEFAULT NULL,
  `privacy` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `width` int(10) unsigned NOT NULL DEFAULT '0',
  `height` int(10) unsigned NOT NULL DEFAULT '0',
  `offensive` int(10) unsigned NOT NULL DEFAULT '0',
  `sourcelocation` char(8) NOT NULL DEFAULT '',
  `autoblog` tinyint(1) NOT NULL DEFAULT '0',
  `extension` char(10) NOT NULL DEFAULT '',
  `filetype` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `conversiontime` float NOT NULL DEFAULT '0',
  `converttime` datetime DEFAULT NULL,
  `sender` varchar(100) NOT NULL DEFAULT '',
  `vhost` int(10) unsigned NOT NULL DEFAULT '0',
  `channel` int(10) unsigned NOT NULL DEFAULT '0',
  `rotation` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `ofilesize` bigint(20) NOT NULL DEFAULT '0',
  `moderationstatus` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `rating` decimal(8,6) DEFAULT NULL,
  `votecount` int(10) unsigned NOT NULL DEFAULT '0',
  `url` varchar(150) NOT NULL DEFAULT '',
  `geo_latitude` double DEFAULT NULL,
  `geo_longitude` double DEFAULT NULL,
  `length` decimal(8,2) DEFAULT '0.00',
  `parentid` int(11) NOT NULL DEFAULT '0',
  `language` char(2) NOT NULL DEFAULT '',
  `author` varchar(100) NOT NULL DEFAULT '',
  `context` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `externalid` varchar(255) DEFAULT NULL,
  `originalsaved` bit(1) NOT NULL DEFAULT b'1',
  `hidden` tinyint(4) NOT NULL DEFAULT '0',
  `commentcount` int(11) NOT NULL DEFAULT '0',
  `approvedcomments` int(11) NOT NULL DEFAULT '0',
  `notdeniedcomments` int(11) NOT NULL DEFAULT '0',
  `lastupdatetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `channelleft` int(10) unsigned NOT NULL DEFAULT '0',
  `originalLocation` char(8) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `upload` (`upload`),
  KEY `vhostupload` (`vhost`,`upload`),
  KEY `vhostmodstatus` (`vhost`,`status`,`moderationstatus`,`uid`),
  KEY `complexfiletype` (`vhost`,`status`,`moderationstatus`,`filetype`,`channel`),
  KEY `vhostcontext` (`vhost`,`moderationstatus`,`context`,`parentid`,`status`,`filetype`),
  KEY `externalid` (`externalid`),
  KEY `externalcomments`.
  KEY `vhostchannel` (`vhost`,`status`,`moderationstatus`,`context`,`channelleft`)
) ENGINE=InnoDB;

Questions

Is there a way to partition the table that would make the most sense?
Does partitioning even make sense?
How do I deal with new data if I do partition?


texts: In my company, while I try to  get the ports opened to connect to my SMTP server from my SQL Server running on Windows Server 2012, I get a reply saying that it is a security concern to open ports from SQL Server hosts to the SMTP server and it cannot be allowed. I'm just trying to understand what could be the security issues in having the Windows server hosting the SQL Server in allowing simple database mail alert notifications?


texts:
I reviewed the SQL Server 2012 technical upgrade guide and also googled for more recommendations.

I am migrating a SQL Server 2008 R2 instance to SQL Server 2012 on a separate machine.

Here is what I plan to do. Please correct and advise your recommendations.

On SQL Server 2008 R2 instance:

I ran the SQL Server upgrade advisor instance.
Take backup after all activity is stopped.

On SQL Server 2012 instance:

Restored SQL Server 2008 R2 backups
Update the database compatibility level to 110
dbcc checkdb  with data_purity
EXEC sp_updatestats;
EXEC sp_MSforeachtable @command1='UPDATE STATISTICS ? WITH FULLSCAN';
DBCC UPDATEUSAGE(dbname);

I will have the logins, job setup separately.

Do I need to rebuild the indexes? Any thing else?

I truly value your input and hope to hear your recommendations.

Thank you.


texts: How to find what is contributing the most to "ORA-01555: snapshot too old" error

texts:
A customer has reported this error more then once in the last three months. What I'm trying to do is figuring out how to identify the procedures within the application that contributed the most in changing database blocks when this error is raised (or eventually other activities on the database from tools not strictly related to the application).
For the sake of clarity, before acting on UNDO size and UNDO RETENTION parameters, I'd like to investigate the application in order to identify bad practices that can raise the probability of hitting this error.
What I know for sure is:

the statement that errored is not the root cause, it's just a victime: here a bit of SQL tuning may make it run faster and avoid the issue
frequent commits (sort of loop like approach) made by concurrent session on tha same tables used by the errored statement, may contribute to hit this problem

How can I investigate the Oracle database in order to identify the procedures at the point #2 ? What Oracle dynamic views or diagnostic views can help me in this quest?
We're facing this porblem on Oracle 12.1 with Diagnostic and Tuning Pack


texts: Error converting NVARCHAR to BIGINT when in SUBQUERY with WHERE clause after invalid records filtered

texts:
I have a table that has an NVARCHAR column that contains data that cannot be converted to BIGINT.  I'm well aware of that and have filtered it out using ISNUMERIC(BB.NVARCHARCOL) = 1.  Despite this, I still get an error when trying to query the data stating that Error converting data type nvarchar to bigint.

The following works fine (no errors reported by SQL):

SELECT *
FROM myNormalTable AA INNER JOIN myBadTable BB ON BB.NVARCHARCOL = AA.MYBIGINTCOL
WHERE ISNUMERIC(BB.NVARCHARCOL) = 1

The following throws the error:

SELECT *
FROM (
    SELECT *
    FROM myNormalTable AA INNER JOIN myBadTable BB ON BB.NVARCHARCOL = AA.MYBIGINTCOL
    WHERE ISNUMERIC(BB.NVARCHARCOL) = 1
    ) ZZ
WHERE ZZ.MYBIGINTCOL = 1234

This variation also throws the error:

SELECT *
FROM (
    SELECT *
    FROM myNormalTable AA INNER JOIN
        (SELECT CAST(NVARCHARCOL AS BIGINT) NVARCHARCOL FROM myBadTable WHERE ISNUMERIC(NVARCHARCOL) = 1) BB
      ON BB.NVARCHARCOL = AA.MYBIGINTCOL
    ) ZZ
WHERE ZZ.MYBIGINTCOL = 1234

Keep in mind that this does not error and returns all the records successfully...

SELECT CAST(NVARCHARCOL AS BIGINT) NVARCHARCOL FROM myBadTable WHERE ISNUMERIC(NVARCHARCOL) = 1

I was able to find a solution, which was to convert my BIGINT to NVARCHAR in the subquery:

SELECT *
FROM (
    SELECT *
    FROM myNormalTable AA INNER JOIN myBadTable BB ON BB.NVARCHARCOL = CAST(AA.MYBIGINTCOL AS NVARCHAR)
    WHERE ISNUMERIC(BB.NVARCHARCOL) = 1
    ) ZZ
WHERE ZZ.MYBIGINTCOL = 1234

If I insert the records into a temporary table:

SELECT CAST(NVARCHARCOL AS BIGINT) NVARCHARCOL INTO #TEMP FROM myBadTable WHERE ISNUMERIC(NVARCHARCOL) = 1

I can use that temporary table in a subquery successfully:

SELECT *
FROM (
    SELECT *
    FROM myNormalTable AA INNER JOIN #TEMP BB ON BB.NVARCHARCOL = AA.MYBIGINTCOL
    WHERE ISNUMERIC(BB.NVARCHARCOL) = 1
    ) ZZ
WHERE ZZ.MYBIGINTCOL = 1234

What in the world is going on?  It seems like SQL refuses to use the subquery to get a smaller result set before running the outer query, it wants to use the whole table regardless of what I do.  SQL Server 2012 Developer Edition


texts: Is this way of MySQL shutdown via bat file sufficient to minimise corruption?

texts:
I had memory issues with using 32bit Xampp so I have installed 64bit components myself as a new server.

Although, I want to make sure I am stopping the MySQL database server properly.

Is this a sufficient way to shut it down so that frequently stopping MySQL in the hectic development stage isn't corrupting the database?

The following is contained in a bat file I wrote.

@echo off
"C:\host\MariaDB\bin\mysqladmin.exe"  -u root -prootpass flush-tables
echo Flushing DB, Hopefully.
TIMEOUT 30
echo Flushed DB, I think!

"C:\host\MariaDB\bin\mysqladmin.exe"  -u root -prootpass shutdown
echo Stopped MySQL service.
pause

Again, it works but I want to make sure I am minimising the chance of corruption from stopping the database server.

Thanks in advance.


texts: Single table or multiple tables for optional keys

texts:
If I have a table with multiple optional keys, is it better to have a single table with nullable keys or multiple tables with not nullable keys.

One benefit I could see is the separate tables would permit all keys to be not null which would be better for data integrity.

SINGLE_TABLE

KEY1       KEY2       KEY3      VALUE
-----------------------------------------
1          NULL       NULL      ValueA
1          2          NULL      ValueB
1          2          3         ValueC

or

TABLE_1

KEY1     VALUE
---------------
1        ValueA

TABLE_2

KEY1       KEY2     VALUE
--------------------------
1          2        ValueB

TABLE_3

KEY1       KEY2       KEY3      VALUE
-----------------------------------------
1          2          3         ValueC


texts:
I have seen these papers:

TurboGraph
GraphChi
BiShard Parallel Processor
G-Store
NXgraph
GridGraph

...and a few others. I am new to databases (other than having used them for web apps without understanding their internals), so I don't have much of a foundation on how to use the disk for storage.

I would like to know in general what these papers are doing for storing the graph on the file system. In taking a brief glance at them, they mention loading relevant subgraphs into memory from disk for efficient updates/querying. Some of them store the edges in one set of files (calling them "shards") and the vertices in another set of files (calling them "intervals"). Some have multiple different IDs, such as the "record ID (RID)" and "vertex ID" in TurboGraph (Figure 1 below).

However, I haven't yet seen a full overview of how all the pieces fit together. Wondering if one could explain that.

Specifically:

How the data might be structured into files for a graph database (at a high level).
What must happen when querying/updating in terms of loading stuff into memory at a high level.

It is unclear to me so far what needs to be loaded into memory, and what specifically the IDs are for. I'm not sure if each page (>= 1MB typically) is loaded into memory and parsed somehow, or scanned by line-by-line, or something similar (basically not sure how the file is parsed/scanned, and if it is parsed into some sort of in-memory data structure or if you can do the graph traversal stuff directly on the file bytes). And I'm not sure what the IDs are for. In RDBMSs the ID is sometimes an incremented integer per table without other meaning. In these papers it seems the ID has more to do with the position of a vertex in a page plus some sort of offsets and such. Also, some papers store a large single line (it seems) for a vertex with all its edges (an adjacency list), but I wonder about if you have thousands or millions of edges per vertex what to do. If one could point out the relevant features to look for then doing further research would help bring this into more clarity.

Thank you so much for your time, I hope that makes sense.

Figure 1.


texts:
I'm running SSMS 2019 on my laptop, connecting to 2 different SQL Servers - 2008 R2 and a SQL 2016 machine. When I'm physically in the building where the servers reside, Intellisense works great.  But on days when I'm working from home, connected through VPN, Intellisense is very spotty.  Usually it completely fails to work at all, but every once in a while, it'll auto-fill a database name as I type, but never a table or field name.  I've tried refreshing the local cache (CTRL+Shif+R), but that hasn't helped.  Any ideas?
As requested below, here are the results of running Test-NetConnection "Emden" -port 1433 over VPN connecion:
PS C:\Users\cjm> Test-NetConnection "Emden" -port 1433

ComputerName     : Emden

RemoteAddress    : 192.168.10.56

RemotePort       : 1433

InterfaceAlias   : Ethernet 2

SourceAddress    : 192.168.11.46

TcpTestSucceeded : True


texts: How to efficiently copy millions of rows from one table to another in Postgresql?

texts:
I have two database tables. One contains hundreds of millions of records. Lets call that one history. The other one is calculated on daily basis and I want to copy all of its records into the history one.

What I did was to run:

INSERT INTO history SELECT * FROM daily

And it did the trick for a while, but it started to get slower and slower as the number of records kept growing. Now I have around 2 million records that need to be copied from daily to history in single operation and it takes too long to complete.

Is there another, more efficient way of copying data from one table to another?


texts: What I can do to speed up Key Lookup in my Query

texts:
I am using SQL2005 and against them I calling query simila to this

    declare @art table (id int primary key, naziv varchar(35) null, sifra varchar(15) null, jm int null);

            insert into @art
            select id, left(naziv,35), left(sifra,15), jm from art a
            where   (a.id = @art_id@ or @art_id@ = 0 )

Select
      s.art_id
     ,sum(kol) as kol
     ,sum(kol*mpc) as MPCI
     /*--and meny others --/
    from doc d
                inner join dokumenti dk on (d.tip = dk.tip)
                inner join sdo s on (d.id=s.doc_id)
        where
                        d.datum between  @do_datuma@ and @do_datuma@
                        and dk.prodaja = 1
                        and (dk.Mal_Vel in (@Mal_Vel@))
                        and (d.skl_id = @skl_id@ or (@skl_id@ = 0))
                        and ((d.skl_id in (select skl_id from skladiste_gruperj  where grp_id = @gr_rj_id@)) or ( @gr_rj_id@ = 0))
                        and (d.par_id = @par_id@ or @par_id@ = 0)
                        and s.art_id in (select id from @art)
            group by s.art_id

This is query is slow when  I take look at Execution plan I see that is Cost of 71 % is taken by
Key Lookup.

What I can do to seed up this query.


texts: SELECT, PROJECT, CARTESIAN PRODUCT and JOIN

texts:
I have the following tables;

DOG    +----------------------------------------------+
       | Name   Type     Age Kennel_name  Owner_name  |
       +----------------------------------------------+
       | Rover  Labrador 4   Bidawee      Smith       |
       | Lassie Alsatian 3   Rescue Dogs  Jones       |
       +----------------------------------------------+

OWNER  +------------------+
       | Name  City       |
       +------------------+
       | Smith Lancaster  |
       | Jones Nottingham |
       +------------------+

KENNEL +-----------------------+
       |   Name        City    |
       +-----------------------+
       | Bidawee     Preston   |
       | Rescue Dogs Sheffield |
       +-----------------------+

I am struggling to understand what the following would do to these tables;

i) select (using DOG)
ii) project (using DOG)
iii) Cartesian product (using DOG and OWNER)
iv) join (using DOG and OWNER)

My understanding is that;

i)   Would simply produce the DOG table again.
ii)  Would produce the DOG table again but ensure there are no duplicate values in the resulting table.
iii) Would produce a table where every value of DOG also ties up to Owner
iv)  I am unsure really what join would do here, would it simply just join DOG and Owner together and add the Name and City fields from OWNER onto the end of DOG?

Could somebody please explain these a bit further and correct me if I have any of them wrong?

Thanks


texts:
We want to build a SQL-Database for a system to manage regulations in different countries for different years. The problem is that the regulations change every year for every country. Like for social insurance you need different data in 2015 than you needed in 2014 and we have companies that enter their data to connect with their social insurance informations for every year.

If I want to build my models on this data I would have to create a different model for every year with different attributes. I can't just use migrations, because I have to keep the historical data and information.

Right now I am researching for the right solution and I would like to know if there is a name for this problem to find some solutions?


texts: Tools for looking at transactions inside of transaction log backup file?

texts: Are there any tools (commercial or open source) for looking at the contents of a transaction log backup file?  Occasionally, get a large transaction log backup file and would like to see the contents / transactions inside the log backup to know what is backing up log shipping.


texts: INSERT performance degradation in SQL Server after a certain number of columns

texts:
I’m working on an application which has a feature of export of tabular data into a specified database table. The app uses INSERT statements to export its data to a target database.

Insert is done through a batched INSERT statement with 100 rows per one SQL INSERT statement (for now I can’t use BULK INSERT or bcp).

I’ve noticed that export time raises disproportionately when the number of columns in the source data exceeds some number (that number is not a fixed one and depends on size of the values, number of rows in each INSERT and so on).

For example, export of 50 000 rows (500 INSERT statements with 100 rows in each) of random strings with 100 chars each and 100 rows per INSERT takes:

3 sec with 5 columns
6 sec with 10 columns
56 sec with 15 columns
77 sec with 20 columns

Note the difference in export time between 10 and 15 columns. I was expecting that export time of 15 columns will be 9-10 sec, but it’s actually 5 times longer. I was able to find similar performance degradation while testing export of other datasets.

In order to be sure that the problem is not on my side, I ran the sames set of INSERT statements through sqlcmd.exe. I got similar results.

Question: What can I do to make SQL Server to work with large number of columns as fast as with a small one? Or at least “move” the point of performance degradation to a larger number of columns?

Additional details:

INSERT queries were executed on a local SQL Server Express 2014 (64-bit), version 12.0.5000.0;
Database recovery model is set to Simple;
All the INSERT statements were wrapped in a single transaction (I tried to call COMMIT after each INSERT, but the results were pretty much the same);
Target table was created before each test. It was a simple table without any indexes, foreign keys, constraints, etc.;
Hard drive performance doesn’t seems to be the source of the problem because during the first two tests (with 5 and 10 columns) disk write speed of sqlservr.exe process was 10 times greater than during the last two cases.

Tables are created like this:

CREATE TABLE [Test_Table]
(
    [Column 1] VARCHAR(255),
    [Column 2] VARCHAR(255),
    [Column 3] VARCHAR(255),
    [Column 4] VARCHAR(255),
    [Column 5] VARCHAR(255)
)

The data looks like this (each cell actually contains 100 chars long string, all the string in the same row are equal):

+------------+------------+------------+------------+------------+
| [Column 1] | [Column 2] | [Column 3] | [Column 4] | [Column 5] |
+------------+------------+------------+------------+------------+
| R6YZ..uWaQ | R6YZ..uWaQ | R6YZ..uWaQ | R6YZ..uWaQ | R6YZ..uWaQ |
| DMNW..Kh0a | DMNW..Kh0a | DMNW..Kh0a | DMNW..Kh0a | DMNW..Kh0a |
| GKbg..yuap | GKbg..yuap | GKbg..yuap | GKbg..yuap | GKbg..yuap |
| pG+f..64bX | pG+f..64bX | pG+f..64bX | pG+f..64bX | pG+f..64bX |
| O2Q7..fTNF | O2Q7..fTNF | O2Q7..fTNF | O2Q7..fTNF | O2Q7..fTNF |

Here are two examples with reproduce the issue:

http://rextester.com/OZI56670 (10 columns, ~0,09 sec)
http://rextester.com/HLAP4972 (11 columns, ~0,45 sec)


texts: "Print" specific columns through foreign keys

texts:
I have three tables:

Users
Users_request
Users_applications

Table Users has the id column, that column is related on Users_request.id finally Users_request.id is related with Users_applications.id

I try to do:

Users table has a column called name and User_applications has application_name.
I want to "print" only those columns with their rows through JOIN clause.

Desired Output:

 user| application_name|
-----|------------------
user1| application1
user2| application2
userN| applicationN

I have the next, but I want improve it not using hardcode Users_applications.id

SELECT Users.id, Users_applications.name
  FROM Users, Users_applications
 WHERE Users.id = 'User1' AND User_applications.id = n ;

Is there any other way to accomplish it, through only Users.id using JOIN clauses?

Thanks in advance.


texts: Ideal MAXDOP & CPUs - how to trace for parallel queries?

texts:
There is a discussion going on in our company on what the ideal CPU count and Max Degree of Parallelism are for a 3rd party database server.

The server has 12 CPUs, 32GB RAM and all database sizes add up to < 30GB so they can all fit in memory (I tried to force this by doing a select * from every table). On certain payroll days, the CPU gets maxed out to 100% for a few seconds.

MAXDOP was originally set to the default 0. We later changed it to 8 based on several 'best-practices' articles. However the vendor suggests to change it to 1 (no parallelism), while others suggest changing it to 4, so that one run-away query doesn't hog most of the CPUs.

I'd like to find out how many CPUs are actually being used by queries. There is a Degree of Parallelism event in Profiler. The BinaryData column says :

0x00000000, indicates a serial plan running in serial.
0x01000000, indicates a parallel plan running in serial.
>= 0x02000000 indicates a parallel plan running in parallel.

What does "parallel plan running in serial" mean ?
I see a lot of 0x01000000, and a few 0x08000000's in my trace. How can i determine whether one query is hogging CPUs and if reducing it to 4 will help ?


texts: Create incremental/differential backups in ProgressDB

texts: Is there a way to create incremental/differential backups in ProgressDB?


texts:
I cannot access oracle from remote due to my listener error. I checked all configuration files which are listener.ora, tnsanmes.ora and sqlnet but I didn't find any missing config.
Below is my listener.ora

  listener.ora Network Configuration File: D:\app\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora

  Generated by Oracle configuration tools.

  SID_LIST_LISTENER =   (SID_LIST =
      (SID_DESC =
        (SID_NAME = CLRExtProc)
        (ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1)
        (PROGRAM = extproc)
        (ENVS = "EXTPROC_DLLS=ONLY:D:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
      )   )

  LISTENER =   (DESCRIPTION_LIST =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = STCDB)(PORT = 1521))
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      )   )

  ADR_BASE_LISTENER = D:\app\Administrator

Here is tnsnames.ora

  tnsnames.ora Network Configuration File: D:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora

  Generated by Oracle configuration tools.

  ORACLR_CONNECTION_DATA =   (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      )
      (CONNECT_DATA =
        (SID = CLRExtProc)
        (PRESENTATION = RO)
      )   )

  ORCLAMS =   (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 10.15.192.18)(PORT = 1521))
      )
      (CONNECT_DATA =
        (SERVICE_NAME = orclams)
      )   )

lsnrctl status:

tnsping

lsnrctl listener start:

Below is my log alert message:

    10-JAN-2019 07:42:21 *
  (CONNECT_DATA=(CID=(PROGRAM=)(HOST=jdbc)(USER=STCDB$))(SERVICE_NAME=orclams))
  * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=59198)) * establish * orclams * 12560
  TNS-12560: TNS:protocol adapter error  TNS-00530: Protocol
  adapter error   64-bit Windows Error: 53: Unknown error    TNS-12518:
  TNS:listener could not hand off client connection
      TNS-12571: TNS:packet writer failure
       TNS-12560: TNS:protocol adapter error
        TNS-00530: Protocol adapter error
         64-bit Windows Error: 54: Unknown error

Actually, my oracle server worked normally before we change server's IP address from 10.15.193.18 to 10.15.192.18. So I have changed new IP address tnsnames.ora then Oracle have been not working properly

Thanks in advance.


texts: How do you filter SQL error logs to display specific error messages?

texts: I find it hard to believe that there aren't better native tools to filter SQL Server Error Logs.  Even Windows Logs have better filtering capabilities.  I'd like the ability to remove certain events from the results or a list of events from the results or to be able to search for a very specific error/event.


texts: SQL SERVER availability groups and virtualized HA

texts:
vSphere and Hyper-v are meant to complement AGs by adding additional HA. However, if I lose a VM hosting a primary sync  replica, wouldn't vSphere/Hyper-v both migrate the failed VM to another host at the sane time as SQL Server automatically fails over to the Sync secondary replica?

Questions
Who would alway win?
Can this screw up SQL?
Wouldn't it be easier to just use SQL for HA (and DR) and migrate the failed VM over to a surviving host  after SQL has switched over to a secondary replica?

Any comments/experience appreciated.


texts: Biological Sequences of UniProt in PostgreSQL

texts:
What is the best way to store UniProt biological sequences in PostreSQL?

Data Details

We pull in 12 million sequences from UniProt - this number is likely to double every 3-10 month.
The length of a sequence can vary from 10 to 50 billion characters
Less than 1% of the sequences are longer than 10 thousand characters

Would it improve performance to store the longer sequences separately?

A sequence can be of either Protein or DNA alphabet

The DNA alphabet has 5 characters (A, T, C, G, or -).
The Protein alphabet will have around 30 characters.
We don't mind storing the sequences of the two different alphabets in different columns or even different tables. Would that help?

Data Access Details

To answer Jeremiah Peschka's comment:

Protein and DNA sequences would be accessed at different times
Would not need to search within the sequence (that's done outside of db)
Would ether access single rows at a time or pull out sets of rows by IDs. We would not need to scan rows. All sequences are referenced by other tables - several biologically and chronologically meaningful hierarchies exist in the database.

Backwards Compatibility

It would be nice to be able to continue to be able to apply the following hashing function (SEGUID - SEquence Globally Unique IDentifier) to the sequences.

CREATE OR REPLACE FUNCTION gfam.get_seguid(p_sequence character varying)
  RETURNS character varying AS
$BODY$
declare
  result varchar := null;
  x integer;
begin

  select encode(gfam.digest(p_sequence, 'sha1'), 'base64')
  into   result;

  x := length(result);
  if substring(result from x for 1) = '=' then

     result := substring( result from 1 for x-1 );

  end if;

  return result;

end;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;


texts: Best way to capture changes on OLTP for Data Warehouse: Create/Update column or CDC

texts:
What is a preferable way to move ETL data into Data Warehouse? Create/Update date on the OLTP application, or Change Data Capture (CDC)?

Given the two are allowed on a system and resourcing is not an issue, I would think CDC is preferable. As sometimes, I have seen software application/developers make mistakes where the Create/Update columns do not work. Just want someone to validate thinking.

Also curious to what Kimball mentioned, seems he prefers CDC here?

https://www.kimballgroup.com/2007/10/subsystems-of-etl-revisited/

https://www.kimballgroup.com/2009/10/six-key-decisions-for-etl-architectures/


texts: MySQL performance issues when saving Bitcoin data

texts:
I have set a cron job to a php script which periodically checks Bitcoin for new blocks, and if there are any, add the blocks and transactions to a database. After a few million records have been added, I have noticed that the table I am saving transactions to is suffering an incredible slowdown. This issue had to do with not having set up my search term (in this case address affected and/or txid) as an index, after adding the index it has become considerably faster.

This leads me to believe that there may be some best practices I am unaware of when saving Bitcoin data to a database, possibly breaking tables down to a set size, so that I can search my database later at optimal speeds.

My question is this: When saving a large data set to MySQL that constantly gets updated, such as the Bitcoin data, what do I need to do to improve the performance for database lookups?

My primary goal is to be able to retrieve data as quickly as possible. Thank you! :)

Edit: Here is the table structure before adding indices.

vin (Hex TEXT, Txid VARCHAR(64), Version VARCHAR(1), Blockhash VARCHAR(64), Locktime VARCHAR(1), Time VARCHAR(10), Vincoinbase BLOB, Vintxid VARCHAR(64), Vinvout VARCHAR(4), Vinscriptsigasm TEXT, Vinscriptsighex TEXT, Vinsequence VARCHAR(12))

vout (Hex TEXT, Txid VARCHAR(64), Version VARCHAR(1), Blockhash VARCHAR(64), Locktime VARCHAR(1), Time VARCHAR(10), Voutvalue DECIMAL(20,8), Voutn VARCHAR(4), Voutscriptpubkeyasm TEXT, Voutscriptpubkeyhex TEXT, Voutscriptpubkeyreqsigs VARCHAR(4), Voutscriptpubkeytype VARCHAR(10), Voutscriptpubkeyaddresses TEXT)


texts:
I have this query, which I don't know why it doesn't work, even though if I change some parameters, it works just fine. Here is the query:

SELECT round(ST_Distance_Sphere(
                       (SELECT coordinates FROM houses WHERE id = 7)),
                       (SELECT wkb_geometry FROM coastline ORDER BY wkb_geometry <->
                       (SELECT coordinates FROM houses WHERE id = 7)) LIMIT 1)
                       )) AS DISTANCE;

  ERROR:  syntax error at or near "LIMIT"
  LINE 4: (SELECT coordinates FROM houses WHERE id = 7)) LIMIT 1)

This works:

SELECT round(ST_Distance_Sphere(
                 ((ST_GeomFromText('POINT(10.806527 54.101916)',4326))),
                 (SELECT wkb_geometry FROM coastline ORDER BY wkb_geometry <->
                 ((ST_GeomFromText('POINT(10.806527 54.101916)',4326))) LIMIT 1)
                 )) AS DISTANCE;

I tried removing LIMIT, but it leads me to problems with the parentheses, and there is where I get lost.


texts: Postgresql: sort by value position in array column, then by secondary order

texts:
I'm not quite sure what the best way to phrase this is...
So in my DB there is pillars text array which is basically an enum where providers ordered what values meant the most to their business, from most important to providing that value for their clients, to least important.
I'm using PostGIS to query providers in a specific area, and want to return providers ordered first by the pillar that a client selected they were looking for, then by closest location.
so if the pillars all have values ['a', 'b', 'c', 'd'], in any order depending on what providers selected, and the client selected pillar c
the results of the query would preferably return any/all providers that have pillar c at array index 0 first, ordered by distance to geopoint, then by providers that have pillar c at array index 1 second ordered by distance to client geopoint, then idx 2, then idx 3
I'm really only looking for the top 3 results in all cases, and providers with pillar c at idx 1 would only be needed if there were less than 3 results for index 0
Is this possible to pull off in a single query? or should I just run it with a where clause and check the results length until I have 3 results?
The pillars column is indexed with a gin index btw


texts: Slave executing queries with different charset

texts:
I need to change a database charset because of this issue and I solved my issue in staging environment with the SQL command

ALTER TABLE `articles` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

In this way I get the content converted to utf8 and I now can insert utf8 strings in those tables. Now I need to apply that fix on production databases which have a master-slave setup.

I created a latin1 unused table to test the command before doing that on real data. In a replicated database I added a table and some rows

-- On master host
CREATE TABLE `test_table` (
  `content` mediumtext NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-- Insert a utf8 only chars, it produces 'H?rvatistan' initially
INSERT INTO `test_table` (`content`) VALUES ('Hırvatistan');
-- Change table charset
ALTER TABLE `test_table` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
-- Insert again the same value in master, this time I get the right value saved
INSERT INTO `test_table` (`content`) VALUES ('Hırvatistan');
-- On master I get
SELECT content, HEX(content), HEX('Hırvatistan') FROM test_table;
+-------------+--------------------------+--------------------------+
| content     | HEX(content)             | HEX('Hırvatistan')       |
+-------------+--------------------------+--------------------------+
| H?rvatistan | 483F72766174697374616E   | 48C4B172766174697374616E |
+-------------+--------------------------+--------------------------+
| Hırvatistan | 48C4B172766174697374616E | 48C4B172766174697374616E |
+-------------+--------------------------+--------------------------+

So everything has worked as expected. However when I go to the slave host I find a strange thing. All these commands has been executed (all of them are present in master binlog) and the table is now utf8 in slave host as well but the last query return a different result set in slave host:

SELECT content, HEX(content), HEX('Hırvatistan') FROM test_table;
+-------------+------------------------+--------------------------+
| content     | HEX(content)           | HEX('Hırvatistan')       |
+-------------+------------------------+--------------------------+
| H?rvatistan | 483F72766174697374616E | 48C4B172766174697374616E |
+-------------+------------------------+--------------------------+
| H?rvatistan | 483F72766174697374616E | 48C4B172766174697374616E |
+-------------+------------------------+--------------------------+

How that is possible?

Update

I tried it again, but this time I waited a little bit before executing the second insert and everything has worked as expected. Maybe in the first attempt second insert was executed before actual table conversion? Is this possible?


texts: SQL Server on Linux, an unmount error is output to the screen upon reboot

texts:
In SQL Server on Linux (Ubuntu 16.04 + SQL Server 2017 RC2), the database is arranged on a different disk from the OS.
(I mounted /dev/sdb1 on /datadir.)

When restarting or Shutdown, the following message will be displayed at restart.

[FAILED] Failed unmouting /datadir

When restarting, I think that unmounting is done before the SQL Server process ends, but what kind of setting should be done to solve this problem?

Is there a setting like changing the order so that unmout is executed after SQL Server service termination or not unmounting the added disk when restarting?


texts:
I'm trying to get the count of child of a parent row. The sql query I'm using as follow only pulls the immediate count, how can I get total count as childs can have more childs.

select  c.id, c.base32_id, c.author, c.message, c.title, c.type, c.datetime,
(select count(*) from comment where parent_id= c.id ) as ChildCount
from comment c where c.parent_id is null order by id asc limit 25

http://sqlfiddle.com/#!15/158ea


texts: Merge replication SQL Server 2008 publisher with SQL Server 2012 subscriber

texts: is this somehow possible to have SQL Server 2008 publisher with a SQL Server 2012 subscriber for merge replication?


texts: Importing a mysql database after drop filling up limited / in /tmp deleted file - A problem?

texts:
I'm importing a fairly large set of databases into a system which has a / partition only 10GB large. After a previous failed import, I dropped the database, but noticed /var/lib/mysql didn't exactly empty out - Now I'm watching the root partition fill up with this file:

$ sudo lsof / | grep deleted
mysqld    53058     mysql   41u   REG  253,0 951599104   5523 /tmp/MLR7HVLj (deleted)

This file is growing large enough (2GB) that I'm starting to worry that it'll crash my system. Is this a known problem (Old centos 6 mysql version 5.1) with trying to import a database after it's been dropped already, and is mysql aware of limited space in the partition it's filling up with a deleted file?


texts: SQL Server Order SELECT List with condition

texts:
I want order the select list with condition. For example; I have t1 table and it has 3 columns and 6 rows

Table t1:
+----+---+---+
| id | x | y |
+----+---+---+
| 1  | a | 1 |
| 2  | b | 3 |
| 3  | c | 2 |
| 4  | d | 2 |
| 5  | e | 1 |
| 6  | f | 3 |
+----+---+---+

Ordering the select list without condition, that select query is easy:
SELECT y FROM t1 ORDER BY y result: 1,1,2,2,3,3
SELECT y FROM t1 ORDER BY y DESC result: 3,3,2,2,1,1

If I want that result 2,2,1,1,3,3 or 2,2,3,3,1,1, how can i do it with select query or c# entity framework?


texts: Where is sp_MSrepl_getpublisherinfo in SQL Server?

texts: In learning about replication in SQL Server and having problems creating a subscription, I looked at the definition for sp_addsubscription. It calls sys.sp_MSrepl_getpublisherinfo, which I cannot find on the server. This stored procedure obviously exists, as it no errors occur regarding it not existing. Where does it live, and can its code be viewed?


texts: Does fast bcp replicate in sybase sql statement replication?

texts: Is it possible to replicate fast bcp using sql statement replication in sybase?


texts: Send SSRS Report Many Times Based on Sql Query

texts:
I have a user who is requesting that a legacy report (email subscription) that runs with multiple categories be modified to run based on which categories exist in the given date range. Thus instead of one email with daily details on:

Widget 1
Widget 2
Widget 3
Widget 4

They would like 4 separate emails with just Widget n.

The kicker is that in any given day, there can be a subset of Widget n details in the schema and they only want an email if details for those widgets exist.

Is this even possible with SSRS? I can do a data-driven subscription I can't quite figure out how to fire the report multiple times for each existing widget in that day.


texts: SQL Server Service does not start automatically after system restart

texts:
I have a SQL Server system that reboots weekly (after installing Windows Updates).

The last three weeks I have seen that the SQL Server service does not start when the server comes back up (Configufation Manager shows the service as stopped) the System Event log shows the following errors:

Event ID 7000

  The SQL Server (MSSQLSERVER) service failed to start due to the
  following error:  The service did not respond to the start or control
  request in a timely fashion.

Event ID 7001

  The SQL Server Agent (MSSQLSERVER) service depends on the SQL Server
  (MSSQLSERVER) service which failed to start because of the following
  error:  The service did not respond to the start or control request in
  a timely fashion.

Event 7009

  The SQL Server (MSSQLSERVER) service failed to start due to the
  following error:  The service did not respond to the start or control
  request in a timely fashion.

However, I can start the service manually and it starts up fine.

The service is set to automatically start.

The SQL Server error log does not include any clues either, unfortunately.


texts: Best practice to delete big portion of big mysql table

texts:
I have a log table in mysql that inserts 1 000 000 rows per day for the past 6 months. (Approximately 180 000 000 rows)

This table requires 54GB of space whereas rest of tables within the same database only require 2GB.

I plan to delete this log table manually using:

DELETE FROM log_table ORDER BY id asc LIMIT 1000000

It is intended to keep approximately 10 000 000 most recent rows.

Should I use some specific procedure such as table optimization, stopping temporarily traffic to the database, use higher / lower limit, make a copy of database to make sure data is not fragmented on the HDD and so on?

Thank you for your help.


texts: postgres streaming replication slave ignores zero_damaged_pages

texts:
I have:

2017-04-19 09:33:26 UTC  :: WARNING:  page 43087 of relation base/11137505/11139144 is uninitialized
2017-04-19 09:33:26 UTC  :: CONTEXT:  xlog redo visible: rel 1663/11137505/11139144; blk 43087
2017-04-19 09:33:26 UTC  :: PANIC:  WAL contains references to invalid pages
2017-04-19 09:33:26 UTC  :: CONTEXT:  xlog redo visible: rel 1663/11137505/11139144; blk 43087

on 3 out of 7 slaves in replication on 9.3.9

The database 11137505 is not important for prod, so I decided to try using a broken db while building up a new slave and so I try to zero_damaged_pages = on to see what else is broken.

Yet it seems to ignore the setting appended to the end of the postgres.conf, producing the same log and terminating the instance.

This option is not meant for such usage - right? I can't make replicated slave to zero the corrupted page and go on replaying?


texts: Why adding SQL Server user to "Perform volume maintenance tasks" improves the speed of database resizing so much?

texts:
If I want to create 5GB database with

CREATE DATABASE [test]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'test', FILENAME = N'E:\2012\test.mdf' , SIZE = 5529600KB , FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'test_log', FILENAME = N'E:\2012\test_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)

it takes 1 minute on my SSD.

But when I add SQL Server user to Perform volume maintenance tasks

it takes only 1-2 seconds.

Why is that? Can someone explain to me what are the reasons for this?


texts: How to take Snapshot of Restoring Database

texts:
I have set up log shipping with my database to make a backup. The backup copy of the database is in a status called '(Restoring...)'.

I understand this is to be expected? Is there a good way I can tell whether the log shipping is actually working?

I have run the stored procedures sp_help_log_shipping_monitor_primary, and sp_help_log_shipping_monitor_secondary which are all good.

What I had in mind was trying to understand what the restoring mode is. If there is a way of backing up and restoring that database so I can take a separate copy of it to check?

In an emergency, how do you take it out of the restoring mode?


texts: MYSQL Find (and Delete) Word Prefixes and Suffixes

texts:
Let's say I have a MYSQL table with the following data in one column:

marine
submarine
way
subway
subjugate
submission
mission

I want to find (and delete) rows that have "sub" as the prefix IF AND ONLY IF another row exists with the root word. So in this example I want to delete "submarine", "subway", and "submission" but NOT "subjugate" (since "jugate" doesn't exist).

How can I do this?

UPDATE: Okay, thank you @Akina. I am getting a:

#1054 - Unknown column 't1.word' in 'where clause'

when I try. This is my version:

SELECT t1.*
FROM `en` AS t1, `en` AS t2
WHERE LEFT(t1.word, 3) = 'sub'
AND t1.word = CONCAT('sub', t2.word)

What am I doing wrong?


texts: Two instances sharing same system database?

texts:
I have a situation that I encountered with one of my clients, that I have never seen before. They are running SQL Server Express 2008 R2. There are two instances of SQL Server installed - a default instance on DATA-SVR, and a named instance on DATA-SVR\Product.

When trying to investigate a problem on the system, I checked the data link files on all workstations, and saw that some users were connecting to DATA-SVR, and others were connecting to DATA-SVR\Product. They were able to do this, because both instances have exactly the same databases. Not good, to say the least.

However, when I checked the database paths between the two instances, they are both pointing at the same data files. I checked that this was the case by creating a table in DATA-SVR, and then accessing the same table from DATA-SVR\Product. They're definitely pointing at the same data, so catastrophic failure was averted. However, to clean things up, I tried detaching the databases from the DATA-SVR\Product instance so that there was only one instance to connect to, but this also detached the database from the default instance.

We have a service running on the server, which for some reason is only able to access the named instance, and not the default instance, so I've now changed all users to use the named instance. Everything is working now. However, I'm still uneasy about how this situation could have come about.

Can anyone shed some light on what is happening here, and how to fix it (preferably without reinstalling the database server)?


texts: For SQL Server, how to fix simultaneous parallel table updates?

texts:
I have to update all records (add Guids) on two (indexed) empty columns of 150 tables, each table with around 50k records (using a script to create 40k updates at once in c# and post it to the server) and exactly 4 existing columns.

On my local machine (16GB RAM, 500GB Samsung 850, SQL Server 2014, core i5) when I try to run 10 tables in parallel it takes a total of 13 minutes, while if I run 5 the process finishes in mere 1.7 minutes.

I do understand that something is busy on the disk level, but I need some help in how to quantify this huge difference in timings.

Is there a exact SQL Server DB view that I can check this discrepancy? Is there an exact way to figure out for a given hardware how many table updates can I run in parallel?? (the real test server has more RAM and 10k rpm disks).

Can anyone point to something that I can improve on the SQL Server to improve the timings for the running 10 tables in parallel?

I already tried increasing the Auto Growth size to 100MB from 10MB which improves the Disk Queue length (from around 5 to 0.1) but it does not actually decrease the total time that much.

I have asked the exact same question on stackoverflow, but not getting any helpful answers so far, so some or any insight/help would be immensely helpful. :)


texts:
Have a question on the difference between just allowing autovacuum to take care of vacuum'ing or having to run a full db vacuum in a certain interval (every night?, every week?).

We used to run a full vacuumdb every night before a full backup.  It used to take around an hour and a half.  Then we stopped and just allowed autovacuum to do it's thing.

Is that a good idea?  Should there still be a vacuumdb run every night, week?

I noticed some processes tonight...

"autovacuum: VACUUM public.large_table (to prevent wraparound)"

Is that normal or is that because we stopped running a full vacuumdb every night?

Thanks all for any insight.


texts: Why MySQL tends to use more and more swap while there is enough RAM?

texts:
I have a server with 64GB RAM and 64 GB swap.
It's running MySQL 8.0.19 and few php scripts.
Here is my config:
innodb_buffer_pool_size=40G
innodb_buffer_pool_instances=4
innodb_buffer_pool_chunk_size=10G
sync_binlog=0
innodb_log_file_size=1G
innodb_ft_result_cache_limit=4000000000
max_heap_table_size=4G
tmp_table_size=4G
innodb_online_alter_log_max_size=512M
range_optimizer_max_mem_size=0
performance_schema="OFF"

This server is used for heavy calculations, maximum 5 scripts running at same time, no users.
The huge swap is necessary for rare cases when some script needs a lot of RAM for itself.
The more server works the more memory MySQL tries to consume. If I don't restart it during few weeks it ends up with 90GB total memory consumed (~60RAM + ~30 in swap). And all of this degrades the performance drastically.
For now I don't know any solution besides simple restart. It clears entire swap and makes MySQL restart with ~40GB RAM consumption.
Do you have any ideas how to make MySQL not use swap that much? Perhaps this happens when some PHP script allocates too much RAM and pushes MySQL's memory into swap, but once that program finishes, MySQL doesn't free swap as I supposed it should do.


texts: Comparing date and timestamp variables in a where clause in a procedure

texts:
I have a variable in an Oracle stored procedure of DATE type (from user interface), and another of TIMESTAMP type (database is more precise than what the user would enter in the code that is executing the stored procedure).

I would like to do something like this: (pseudo code)

select * from MYDATABASE where inputDate = extract(date from myDatabaseTimeStamp);

but I get a compile error when I try this.  Any idea what is a good actual code solution for this?


texts: I know there is ADRCI purge command since 11g, but how can I delete part of the alertlog without ADRCI for a specified time period, I want delete everything older than 15 days.


texts:
I'm using Postgres on AWS RDS.

My application is encrypting selected columns in a table using AWS KMS encryption.

I tried but could not find a way to configure KMS keys and use the decrypt() in select query.

For example, my app is encrypting myTable.secureColumn using KMS.

Now, I'm trying to fire below query:

select * from myTable where decrypt(secureColumn, 'key', 'aes') = 'data';

I didn't find how to configure KMS keys in pgCrypto and use in above query.

Has anyone tried this?

Thanks in advance.


texts: How to search for a specific column name in all the tables in MySQL Workbench?

texts:
In MySQL Workbench, is it possible to search for a specific column name in all the tables?

(Writing the string to look for in the field at the top right does nothing).

Thank you.


texts: Create table from another table in Oracle Procedure

texts:
I have a SQL Server stored procedure wherein I am creating a table using another table.

Select Distinct
 col1
,col2
,col3
,col4
into table_2
from table_1;

Above statement will create a new table as ' table_2' with the selected list of columns.

I tried to convert the above query into Oracle syntax:

Create table table_2 as
select
 col1
,col2
,col3
,col4
from table_1;

This is working fine as expected when I am running it in the editor. However, the same statement is giving me errors when I am trying to use this in Oracle Procedure.

I have gone through some links or so, which says that need to create some temp variables or storage or loop in to perform the same task.

I am getting very confused now and don't know how to convert this simple statement into valid Oracle syntax.


texts: Unable to fix ROW size too large even with innodb_file_per_table

texts:
I am having a row length error I've been unable to find a solution for. The error messages are all variations on "Row size too large"

Error executing 'CREATE TABLE IF NOT EXISTS `db`.`table`
Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline..
    SQL Error: 1118

We might just be hitting the limits about we can do in the way our
database is currently constructed. I’ve seen
many
recommendations in similar
situations, nothing has worked.

I realize when it comes to large row size a better solution is vertical partitioning. But at the moment restructuring the database is not an option. Is it possible to fix this in configuration? Some of the answers to similar questions make it seem like it is.

In lots of reports I found people were using VARCHARS, which end up
increasing the row sizes, and people recommended moving to TEXT or BLOB,
in some of our problematic tables we’re using LONGTEXT, I tried converting them all to TINYTEXT and still ended up with my row size issue

What I have tried unsuccessfully, on multiple servers, some of which were with fresh installs of MySQL and fresh databases.

First always make sure the following set, before creating any databases:

innodb_file_format              = Barracuda
innodb_file_per_table           = 1

Then a few of the various changes I tried:

Increase the log file size up to 8G -
‘innodb_log_file_size = 8G’
‘ROW_FORMAT=COMPRESSED’ on an individual table - I was unable to get this to work.
‘innodb_default_row_format = dynamic’ globally
‘internal_tmp_disk_storage_engine=MyISAM’ - Bug
report
Increase page size ‘innodb_page_size = 64K’ - Doc
info

Complete innodb settings:

innodb_file_format              = Barracuda
innodb_large_prefix             = 1
innodb_file_per_table           = 1
innodb_flush_log_at_trx_commit  = 2
innodb_thread_concurrency       = 16
innodb_write_io_threads         = 16
innodb_read_io_threads          = 16
innodb_buffer_pool_size         = 256M
innodb_log_file_size            = 8G

I did also find a bit of information on a bug
report saying that the error
is incorrect, and it was supposed to have been corrected, but I’m not
sure if that applies here. Maybe we are actually ok, and it can be
ignored? When he says
it’s not a bug, I’m not sure he means it’s not an error?

[3 Jun 2014 19:28] Daniel
Price

  The “Row size too large (> 8126)” error is not a bug.


texts: We have a relatively busy site with ca. 30.000 connections per day. Things run fine, except that 1 or 2 times per day the MySql server crashes for no apparent reason and then restarts automatically. In the error logfile we see that it says that some tables are marked as crashed, but actually they have not, they are all still fine and usable. In the logfile I have added here, it can be seen that we have twice a strange 'Got timeout reading communication packets' from suspicious sources, but they are 5 hours, respectively about 1 hour before the actual crash happens at 06:25.10. The last message with a crashed table is seen 28 minutes before the crash happens, so its probably not related? Could it be that one of the 2 warnings that can be seen is triggering the crash?