texts: Oracle server connection reset by peer socket write error

texts:
Full disclosure, I'm not a database admin, but if anyone is able to help me, it would be you guys.

Our Oracle 11g database server stopped working, I'm not aware of anything that could have caused it, it was out of the blue. When I try to connect via SQL Developer I get either 'connection reset by peer socket write error' or 'Input/Output error: connection reset.

I've tried restarting it, checked whether the Windows Services are running and went through every item in the configuration that I could have found, but nothing helped. It seems that the listener is unable to start (I've tried adding a new listener with identical setting and also got the socket write error)

Since I've done no changes to the configuration, I'm perplexed what the issue could be. Does anyone has any ideas to what could be the cause of this issue? And if not, can some one post a link to something like the SQL Server's "Repair Installation" which I didn't find?

Any help is appreciated.

EDIT: I was asked for the following:

Output for the lsnrctl status:

    LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 03-JUL-2012 13:30
:53

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-12571: TNS:packet writer failure
 TNS-12560: TNS:protocol adapter error
  TNS-00530: Protocol adapter error
   32-bit Windows Error: 54: Unknown error

And according to the lsnrctl start, the listener is already running


texts: install FirstResponderKit in Azure SQL DB?

texts:
I've just downloaded the newest version (20190128) of FirstResponderKit from BrentOzar.com, and tried to install it on Azure SQL DB.
I'm getting these errors:

Msg 40515, Level 15, State 1, Procedure sp_AllNightLog, Line 16 [Batch Start Line 13]
Reference to database and/or server name in 'master.sys.procedures' is not supported in this version of SQL Server.
Msg 40515, Level 15, State 1, Procedure sp_AllNightLog_Setup, Line 16 [Batch Start Line 1492]
Reference to database and/or server name in 'master.sys.procedures' is not supported in this version of SQL Server.
Msg 40515, Level 15, State 1, Procedure sp_Blitz, Line 16 [Batch Start Line 2801]
Reference to database and/or server name in 'master.sys.all_objects' is not supported in this version of SQL Server.
Msg 40515, Level 15, State 1, Procedure sp_BlitzLock, Line 16 [Batch Start Line 28063]
Reference to database and/or server name in 'msdb.dbo.sysjobs' is not supported in this version of SQL Server.
Msg 40515, Level 15, State 1, Procedure sp_DatabaseRestore, Line 16 [Batch Start Line 35912]
Reference to database and/or server name in 'master.sys.xp_dirtree' is not supported in this version of SQL Server.

It installs nicely on my SQL Server 2008.

Do FirstResponderKit work on Azure?

Edit:
Most of it runs, and I've started to change sp_blitz, so that it will run as well. please see my github if you want to test it.

Edit2:
I've run it against a new Azure SQL database that I've moved from on-prem. It found two issues for me (caused by Microsoft Data Migration Assistant) about statistics not updated, and many indexes that were disabled. I think that other people will find similar issues, if we could get this version into the main release.
Testers Needed!


texts: How to make 6 partitions of table by 6 months and 7th partion of older than 6 month data?

texts:
we have a table structure with periodical data in a time-stamp column.
how to make 7 partition of this table with 7th portion as data older than 6 months.

I have already thought of running a cronjob and manually transferring the data to archive table if the above solution does not work out.


texts: How to run ALTER TABLE on a list of tables sequentially in a function

texts:
I have a schema (called import) where data are imported as tables, and a function that processes them all. After that I want to move these tables away in another schema (called data_archive). The existing function works great but I can't find a way to change the schema; here's a function I wrote to do this:

My idea was to create a list of tables according to criteria provided by a select on a table made for this management, then loop over this list and do a Alter table xxxx SET schema data_archive:

CREATE OR REPLACE FUNCTION archive_datasets()
  RETURNS VOID AS
$BODY$
DECLARE
   table_rec  record;

BEGIN

   FOR table_rec IN
      SELECT t.table_name
      FROM information_schema.tables AS t
      WHERE table_schema = 'import'
      AND quote_ident(t.table_name) IN (
              SELECT "table_name"
              FROM data_sets
              WHERE status IN ('Processed', 'Archived', 'Deleted')
              )

   LOOP
     ALTER TABLE quote_ident(table_rec.table_name) SET schema data_archived;
   END LOOP;

END;
$BODY$
  LANGUAGE plpgsql;

But I obtain this error message:

ERROR:  syntax error at or near "("
LINE 28:  ALTER TABLE quote_ident(table_rec.table_name) SET schema ...

So my question is how to pass the name of the table to this ALTER TABLE query? Or what am I doing wrong?

I use PostgreSQL 9.3.


texts:
Database noob here, just curious to see if my design could benefit from any improvements.

My goal was to keep this as simple as possible: a user can create an account, two accounts can message each other.

That's it.

To achieve this, I've use the following schema:

CREATE TABLE account (
    id         INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    email      VARCHAR(255) NOT NULL,
    first_name VARCHAR(255) NOT NULL,
    last_name  VARCHAR(255) NOT NULL,
    --
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    --
    PRIMARY KEY (id),
    UNIQUE (email)
);

CREATE TABLE message (
    id      INT UNSIGNED NOT NULL AUTO_INCREMENT,
    to_id   INT(10) UNSIGNED NOT NULL,
    from_id INT(10) UNSIGNED NOT NULL,
    body    VARCHAR(500) NOT NULL,
    seen    BOOLEAN NOT NULL DEFAULT 0,
    --
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    --
    PRIMARY KEY (id)
);

Using this schema, I want to be able to run two queries:

A snapshot. I want the message overview, similar to how Facebook does it; your most recent messages in each thread are listed.
A thread. I want to show the thread of messages between two users in a list.

I achieved a paginated list of #1 with the following query:

SELECT message.*, `from`.*, `to`.*
FROM message
LEFT JOIN account AS `to`
    ON `to`.id = message.to_id
LEFT JOIN account AS `from`
    ON `from`.id = message.from_id
WHERE message.id IN(
    SELECT MAX(m.id)
    FROM message m
    WHERE m.id < 999999999
        AND m.to_id = 1
        AND m.deleted_at IS NULL
    GROUP BY GREATEST(m.to_id, m.from_id), LEAST(m.to_id, m.from_id)
)
ORDER BY message.seen, message.id DESC
LIMIT 100

The query above for user with ID 1 will show me a snapshot of latest messages received from every user.

I achieved a paginated list of #2 with the following query:

SELECT message.*, `from`.*, `to`.*
FROM message
LEFT JOIN account AS `to`
    ON `to`.id = message.to_id
LEFT JOIN account AS `from`
    ON `from`.id = message.from_id
WHERE message.id < 999999999
    AND (message.to_id = 1 AND message.from_id = 2) OR (message.to_id = 2 AND message.from_id = 1)
    AND message.deleted_at IS NULL
LIMIT 100

The query above will give me all messages between user with ID 1 and user with ID 2.

Any thoughts? I'd love to hear of some obvious areas of improvement, and some possible explanation as to why so I can learn from it.

Cheers!


texts: Maintenance Plan job failing with access denied for NT SERVICE\SQLSERVERAGENT

texts:
I used the Maintenance Plan Wizard under SQL Server 2014 to create a simple maintenance plan that once a week:

Check Database Integrity including indexes
Rebuild Indexes
Clean Up History

When the job I get below error message. Any ideas why it is failing? I did check and NT SERVICE\SQLSERVERAGENT is a member of the sysadmin group as it should be

  Executed as user: NT SERVICE\SQLSERVERAGENT. ...2 01:01:31.89
  Source: Check Database Integrity      Executing query "USE [vc2]  ".:
  50% complete  End Progress  Error: 2016-05-12 01:01:51.89     Code:
  0xC002F210     Source: Check Database Integrity Execute SQL Task
  Description: Executing the query "DBCC CHECKDB(N'vc2')  WITH
  NO_INFOMSGS  " failed with the following error: "A database snapshot
  cannot be created because it failed to start.  A database snapshot
  cannot be created because it failed to start.  CREATE FILE encountered
  operating system error 5(Access is denied.) while attempting to open
  or create the physical file 'E:\vc2.mdf_MSSQL_DBCC29'.  The database
  snapshot for online checks could not be created. Either the reason is
  given in a previous error or one of the underlying volumes does not
  support sparse files or alternate streams. Attempting to get exclusive
  access to run checks offline.  The database could not be exclusively
  locked to perform the operation.  Check statement aborted. The
  database could not be checked as a database snapshot could not be
  created and the database or table could not be locked. See Books
  Online for details of when this behavior is expected and what
  workarounds exist. Also see previous errors for more details.".
  Possible failure reasons: Problems with the query, "ResultSet"
  property not set correctly, parameters not set correctly, or
  connection not established correctly.


texts: Difference between Handler_write and Innodb_data_writes

texts:
Handler write is the number of requests to insert a row in a table.
InnoDB data writes is the total number of data writes.

Questions are

Does inserting a row count as a disk write?
If I do an insert operation in MYISaM engine which will get incremented?
If I do an insert operation in InnoDB engine which will get incremented?
If Handler_write is the number request to insert a row then why does it gets incremented every-time I run select queries.

mysql> select VARIABLE_VALUE as value from information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME="Handler_write";
+--------+
| value  |
+--------+
| 195556 |
+--------+
1 row in set (0.00 sec)

mysql> select VARIABLE_VALUE as value from information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME="Handler_write";
+--------+
| value  |
+--------+
| 195557 |
+--------+
1 row in set (0.00 sec)

mysql> select VARIABLE_VALUE as value from information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME="Handler_write";
+--------+
| value  |
+--------+
| 195558 |
+--------+
1 row in set (0.00 sec)


texts: How do you remove fragmentation from InnoDB tables?

texts:
I have a Database having number of tables.

I want to delete some records from the tables say the no of records are more than 20K or 50K.

The All the Tables are InnoDB. And file_per_table is off.

When I will delete the records from a number of tables there will be fragmentation in the tables.

Is there any way to remove the fragmentation.?

Update On 17th April

mysql> select TABLE_NAME, TABLE_SCHEMA, Data_free from information_schema.TABLES where TABLE_SCHEMA NOT IN ('information_schema', 'mysql') and Data_Free >0;
+-----------------+--------------+-----------+
| TABLE_NAME      | TABLE_SCHEMA | Data_free |
+-----------------+--------------+-----------+
| City            | world_innodb |   5242880 |
| City_Copy       | world_innodb |   5242880 |
| Country         | world_innodb |   5242880 |
| CountryLanguage | world_innodb |   5242880 |
| a               | world_innodb |   5242880 |
| t1              | world_innodb |   5242880 |
| t2              | world_innodb |   5242880 |
+-----------------+--------------+-----------+
7 rows in set (0.00 sec)

So Now my Question is that how i will decide that my tables are fragmented or not.


texts:
I'm having troubles to import one of the SQl dumps of Wikipedia into a local database. I've downloaded the categorylinks.sql from https://dumps.wikimedia.org/enwiki/latest/ which is around 17GB when unpacked and I tried to load it into a local MySql database via the command line with mysql -uroot > path/to/file
I monitor the progress my using pipeviewer in front of the command and it begins with ok speed (1-2MB/s) but it gets slower and slower over time, currently sitting at 18KiB/s after a weekend of running and it gives me an ETA of 4 days and that ETA is rather rising then going down.

The first few INSERT statements run at this speed:

Query OK, 7352 rows affected, 64 warnings (0.48 sec)
Records: 7352  Duplicates: 0  Warnings: 64

Query OK, 7145 rows affected, 64 warnings (0.71 sec)
Records: 7145  Duplicates: 0  Warnings: 64

Query OK, 7139 rows affected, 64 warnings (0.66 sec)
Records: 7139  Duplicates: 0  Warnings: 64`

And currently, after 3 days it slows down to:

Query OK, 7166 rows affected, 64 warnings (5 min 25.89 sec)
Records: 7166  Duplicates: 0  Warnings: 64

Query OK, 7013 rows affected, 64 warnings (5 min 8.13 sec)
Records: 7013  Duplicates: 0  Warnings: 64

The warnings are all just "Invalid utf8mb4 character string", so those should not be the problem, right? And the duplicate checks are turned of by setting unique_checks to 0.

I've already tried to tweak many options, most importantly setting autocommit, unique_checks, and foreign_key_checks to 0. Also have set innodb_flush_log_at_trx_commit = 2 as well as playing around with the values of innodb_buffer_pool_size, innodb_log_buffer_size, innodb_log_file_size, innodb_write_io_threads.

Neither RAM (16GB), nor I/O write speed are a problem as the process does not use nearly the available resources according to htop and iotop.
I've read of other people importing >60GB of data in less than 4 hours, so it should be possible to speed it up somehow?

Do you have any recommendations on what other options to change?

Would it be an option to split the SQL file into many smaller files and import them separately? Can you do that parallel?

Thank you very much in advance!


texts: Unable to copy a DB from SQL Server 2000 to SQL Server 2012 using copy wizard

texts:
I am trying to copy a database from SQL Server 2000 to SQL Server 2012 using copy wizard in my local machine where SQL Server 2012 is installed. But, the wizard is not detecting destination server. Source DB is on a network and Destination is my local machine itself.

When I select the destination server as my local machine, I am getting the below error:

  TITLE: Copy Database Wizard

  The destination server cannot be a SQL Server 2005 or later Express instance.

Does Copy Database wizard not support if 'source' is earlier than SQL Server 2005 ? But the error message says only about 'Destination'. Here my destination is SQL Server 2012 and Source is SQL Server 2000.


texts:
I am looking to create an Asset Management System to track assets (laptops/desktops, monitors, keyboards, mice, bags, software keys, etc), basically anything that can be assigned to an employee. My issue is, each type needs to track different attributes. We would track a different set of properties / attributes for computers than say a monitor. I've read about EAV pattern (or anti pattern from what most people say) and it seems like that should be avoided by the plague. Creating a single table with all the columns just seems ridiculous, and creating a new table for each new type that comes along doesn't seem optimal. I recently read about JSONB in Postgres 9.4. Storing a JSON object that can be handled by the application seems like a decent compromise.

Is this a good use case for JSONB? Or does EAV make sense? Or making a giant table, or creating a table per type?


texts: How to configure multi-site SQL Server 2014 Always On Availability Group for DR

texts:
We have a two-node SQL Server AlwaysOn Availability Group cluster configured in one of our datacenters, Datacenter A, and for disaster recovery we are looking to expand this to two additional nodes in Datacenter B.  The two nodes in Datacenter A are configured for synchronous replication; our plan is to configure the replicas in Datacenter B for async replication.  The two datacenters have different networks, but there is cross-site routing (and some sort of dedicated link -- it appears to have good bandwidth and at least decent latency).

It seems broadly that this configuration is supported (also see the description of the StackExchange database configuration), but we're a little unsure of the particulars of the network configuration.

Do the DR nodes need to be joined to the existing WSFC, or will they form their own WSFC?  We currently have a fileshare witness in the cluster for HA automatic failover (hosted on a separate, non-database server in Datacenter A).  If the DR nodes join the existing WSFC, do they also need to see the fileshare witness in Datacenter A?  We don't necessarily want the DR nodes to have any quorum votes as we will only ever manually fail over.  Should we add a fileshare witness hosted in Datacenter B, and does that then need to be reachable by all nodes in the cluster, including those in Datacenter A?  If the DR nodes join the existing WSFC, will the WSFC then have one cluster IP from each network?  Note that we don't want inter-network routing within the cluster; if a client tries to connect to a cluster address from Network B while the primary replica is currently in Network A, we want that connection to fail; we don't want any of the nodes in Datacenter A to have addresses bound from Network B.  Or at least, this is my understanding, we had some concerns from our network team about introducing routing loops if nodes are binding non-local addresses.
As I understand it, the Availability Group Listener will have one IP address from each network; our application then specifies both IPs in the connection string and includes MultiSubnetFailover=True; it is then basically up to the client to determine which network is accepting connections (similar to the older, simpler database mirroring configuration in which the client connection string would specify the failover partner in case the primary IP didn't respond).  Is this correct?  And clients in each network would be able to connect to nodes in both its local network and the remote network?  (In this case it might make sense to alter the connection strings for all clients so that for each client its "local" AG Listener IP is listed first)
At some point in the distant future, we may wish to swap the roles of the datacenters; that is, Datacenter B becomes the primary and Datacenter A becomes the DR site.  In that situation, we would plan to reconfigure the Datacenter B nodes to have synchronous replication, add a fileshare witness in Datacenter B (to enable automatic failover, if we haven't already), and then alter the replicas in Datacenter A to be asynchronous.  Is this reasonable to do with this configuration, and can all of these operations be done fully online (or at least with minimal downtime)?


texts: Many tables v/s one table with different entities types

texts:
My requirement is as follows: App with internationalization and mobile v/s desktop app text support. So, I have came up with below columns in the table which I will certainly need:

    "CODE" VARCHAR2(30 BYTE) NOT NULL ENABLE,  //Unique identifier
    "TEXT_EN" VARCHAR2(1000 BYTE),  //EN text for desktop version of app
    "TEXT_FR" VARCHAR2(1000 BYTE),  //FR text for desktop version of app
    "TEXT_EN_SHORT" VARCHAR2(500 BYTE), //EN text for mobile version of app
    "TEXT_FR_SHORT" VARCHAR2(500 BYTE),   //FR text for mobile version of app

Now, as per the device type and language I will fetch data from appropriate column, I am clear in this part.

Confusion: I will have several drop downs in the app like country, province, medicine names etc. So, should

I have one more field in the same table "FIELD_TYPE" which will identify whether this particular row is country or province etc. So, to fetch all country details my query will have  FIELD_TYPE = "Countries"
Or I should have different table for each - country, province, medicine names etc.

Which is more space efficient and query performance efficient and WHY?

Here is the related question posted by myself and kept is separate to isolate the concern, but you may choose to post your overall answer in either question.

I know the probable choice in each case, but I want to weigh and understand the pros and cons in each case, and why I should choose one and not other!

It is discussed here but for same w.r.t entities.


texts: Query resource quota for PostgreSQL accounts

texts:
Within the latest (12, 13) versions of PostgreSQL, is there any throttling support on the user-account level, to limit how much data any query can produce and/or how much time + memory it may consume under that user account?
I want to limit specific user accounts to allow up to certain amount of data and/or time to consume within the server, before erroring with "query quota exceeded" or something like that.
Example
I want to configure user account guest to produce "quota exceeded" failure for any query that returns more than 10kb of data or takes more than 10 seconds to execute.


texts: Efficient settings for linked server to SSAS database

texts:
We are using OPENQUERY scripts to run MDX from sql-server. We're experiencing efficiency issues when we first run these queries each morning.

The linked server objects which link to our SSAS databases are set as follows.

Are these the most efficient setting for this context?


texts: Can I configure MySQL's typecasting to consider 0 != 'foo'?

texts:
This article points out the following security problem with MySQL:

USERS
id | email            | password_reset_token
1  | one@example.com  | QTlXww)uV!Hdg3U6aGwKV2FPvAqzVgPx
2  | two@example.com  | CZor5t7WbX#LTeqiG3v@6f3@z#)BfK*n

Here we have nice, random tokens that a user must prove they have in order to reset their account. But the user manages to submit a reset token of 0, so we run this query:

SELECT * FROM `users` WHERE `email` = 'one@example.com' AND `password_reset_token` = 0

MySQL converts the token VARCHAR to an INT in order to make the comparison. It considers a VARCHAR that does not start with a number to equal 0. Therefore, an attacker can match any non-numeric string and take over the account.

Even if the reset token starts some numbers, the situation is not much better. MySQL ignores all characters but the initial numbers when making this comparison, so it considers 12blahblah3blah4 to be equal to 12, which makes guessing exponentially easier.

Can I configure MySQL not to do this kind of typecasting? If, for example, it cast INT to VARCHAR instead of vice versa, this attack would not work.

Note

If the query is run with '0' instead of 0, this doesn't work. The article discusses this vulnerability in terms of Ruby on Rails' acceptance of XML, where a type=integer property convinced Rails to send an actual integer in the query.

That bug has been patched in Rails; it now converts all request parameters to strings, so it would never build a query with an integer. But I still think MySQL should be configurable to prevent this.


texts: Replication to SQL Server and to Oracle not working for one database

texts:
I have a replication for many databases on one server to one SQL Server database and to Oracle. Replication for one database is not working while the other four databases are working. For SQL Server to SQL Server, there is one publication. For SQL Server to Oracle, there are 6 publications.

Also CDC is enabled on those databases.

The error messages are:

  Process could not execute 'sp_replcmds' on 'DBname'
  Failed to insert rows into Change Data Capture ....
  The statement has been terminated.

When I look on the replication monitor tabs, Publisher to Distributor history is failing but Distributor to Subscriber history is working. The agent is failing.


texts: SQL Server Replication Monitor Throwing "Specified cast is not valid. (ReplicationMonitor)" Error

texts:
The publisher is running SQL Server 2008 R2 on a Server 2008 R2 box. The subscriber is running SQL Server 2012 on a Server 2012 box. I have tried launching the replication monitor from both servers but can't drill down into details on either instance.

Any ideas on what is going wrong here? I really need to be able to drill down into the details of each description


texts:
While testing query:

select AccountID, SchemaID, Name, Description, CurrentVersion
from [test].[dbo].[FormSchema]
where SchemaID like '%[^a-zA-Z0-9-_]%'

After running execution plan it is showing clustered index scan with 100% cost.  There is one clustered index on column schemaId.

My question is how can I make it index seek rather than scan?


texts: How do I make SQL Server database accessible to PC's on the LAN?

texts:
We are using Microsoft SQL Server 2008 R2 Express on server machine with instance name abc12. This instance contains two databases:

alarm
report

but when we connect to the abc12 instance from other PC's on the local area network, it shows only one database alarm in this instance. It doesn't show the report database.

How do I make that report database accessible to other PC's on the LAN?

Please help me


texts: Adding constraint to an existing table column

texts:
I have a table that doesnt have any constraint on them like this

CREATE TABLE SomeTable
(
   Id int NOT NULL,
   Type varchar(50),
)

insert into SomeTable values (12,'Exchange-Student');

Now I know that I can do alter into add constraint and then check if type is of my desired value.
I just dont know how to implement it correctly.

For example lets say that I want it to be restricted to 3 types 'Exchange-Studen', 'Independent student' and 'program Student'

How would I implement that ?


texts: How to drop all of my functions in PostgreSQL?

texts:
Right now I have to use query to get the command in text file.
Then remove double quote from them. And finally, run that file in psql shell.

How can drop all of my functions in PostgreSQL in a single step?


texts: How do I find PostgreSQL's data directory?

texts:
I forgot how I started PostgreSQL the last time (it was months ago) and I don't remember where the data directory is located. The postgres command seems to require the location of the data directory.

I'm on MacOsX if that helps.

/usr/local/postgres doesn't exist on my Mac.

Using the answers provided below, I found that it was here:

/usr/local/var/postgres


texts:
I want to perform a database restore on SQL Server 2012 and copy the database to a different server. CDC is enabled on the original database.

I have managed to copy the database along with the CDC data using:

Restore Database dB4_res
from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLSERVER2012\MSSQL\Backup\dB1_backup.bak'
with move 'dB1' to 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLSERVER2012\MSSQL\DATA\dB4_res.mdf',
move 'dB1_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLSERVER2012\MSSQL\DATA\dB4_res_log.ldf',
move 'dB1_CT.ndf' to 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLSERVER2012\MSSQL\DATA\dB4_res_CT.ndf'
with keep_cdc

where CDC data is stored in a different file db1_CT.ndf

This restores the CDC data in the new database. However it does not create the capture and cleanup jobs, required for future CDC operations.

So I create the capture and cleanup jobs as:

exec sys.sp_cdc_add_job 'capture'
GO
exec sys.sp_cdc_add_job 'cleanup'
GO

Now this operation deletes all past CDC data from the Change Tracking tables.

Please can anyone suggest how to fix this issue.

Thanks


texts: Azure SQL DB: Can't create table because it already exists, can't drop or write to table because it doesn't exist

texts:
I have an Azure SQL DB database that I am copying data into from a source database using SSIS. My SSIS package for one table fails because it can't write to or create a specific table name (it is important to keep the same table names as in the source database). I attempted to address the problem via an SQL query, but

CREATE TABLE [schemaname].[tablename]

returns:

There is already an object named 'tablename' in the database.

and

DROP TABLE [tablename]

returns:

Cannot drop the table 'tablename', because it does not exist or you do not have permission.

I have had a look at a few posts where other people have had similar problems, but the solutions offered are either not possible in this situation or do not work: I am already accessing the database with the admin account, and there are no tables with the same name as this in any other schema either. Dropping the table if it exists gives the same error, and FLUSH TABLES seems to be MySQL-specific so does not work. Because it is an Azure database, I can't access the back-end filesystem, and the database is too large to easily move or copy anywhere else.

Does anyone have any ideas as to how to resolve this?


texts:
Is there a way to check and see if a query finished?  I ran 3 very long running update queries (+/- 25 hours each) as I went out the door for vacation last week.  Unfortunately, somewhere during the week the power went out and the machine running MYSQL shut down. Is there a way to check and see which of the 3 (or all three) queries were completed?

I know I could check to see if the data had been updated, but NULLs are to be expected with a correct and complete execution and there's 48 million rows of data to peruse.  Any thoughts?


texts: Is a unique "title" field against the 3rd normal form

texts:
Let's say I have a table Game, which has some random attributes, with a name, that has a unique constraint.
The Game entity contains a relationship to ExtendedGame, which contains some other data regarding the same game.
They both have a unique title identifying them.
Game

id
title
other_value

1
game1
123

2
game2
456

3
game3
789

Extended Game

id
title
foo

1
game1
bar

2
game2
cuz

3
game3
fop

A relation is in third normal form if there is no transitive dependency for non-prime attributes as well as it is in second normal form.

Following this principle is it alright to have two tables with a relationship that both have?
Would a relation like this be compliant with the third normal form?


texts: Eliminating effectively-redundant entries with Inner Join

texts:
The problem that we are trying to solve can be explained with the following illustrative example.
We have the following two tables:
CREATE table moniker_tbl
(
  id INT UNSIGNED AUTO_INCREMENT,
  name VARCHAR(20),
  nickname VARCHAR(20),

  PRIMARY KEY(id)
);

CREATE table student_tbl
(
  id INT UNSIGNED AUTO_INCREMENT,
  name VARCHAR(20),
  stipend INT,

  PRIMARY KEY(id)
);

These tables are populated as follows:
INSERT INTO moniker_tbl(name, nickname) VALUES ('John Doe', 'Jonny'),
('John Doe', 'The Doe Man'), ('Richard Stevens', 'Rickie'),
('William Smith', 'Billy'), ('William Smith', 'Smithie');

INSERT INTO student_tbl(name, stipend) VALUES ('John Doe', '5000'),
('Richard Stevens', '7000'), ('William Smith', '6000');

We perform the following INNER JOIN operation:
SELECT t1.nickname, t2.stipend
FROM moniker_tbl AS t1
INNER JOIN student_tbl AS t2
ON t1.name = t2.name;

We get the following results:
+-------------+---------+
| nickname    | stipend |
+-------------+---------+
| Jonny       |    5000 |
| The Doe Man |    5000 |
| Rickie      |    7000 |
| Billy       |    6000 |
| Smithie     |    6000 |
+-------------+---------+

We would like the results to show only one entry per nickname e.g.
+-------------+---------+
| nickname    | stipend |
+-------------+---------+
| Jonny       |    5000 |
| Rickie      |    7000 |
| Billy       |    6000 |
+-------------+---------+


texts: Table with relation to two others (three-way relation?)

texts:
I stumbled across a weird situation that one table has relation to two others but record in it can only have a relation to one of the tables. To clarify things, I have tables: attribute, visit, visit_action. visit is a parent of visit_action - on one visit a user can perform multiple actions. Both visits and actions have attributes that are made of name and value. One attribute can be related either to visit or visit_action but never to both at the same time. One visit can contain of multiple attributes and so does action. How should I keep those relations in database?

Option 1
Create two connecting tables like visit_attribute and visit_action_attribute both with relations to attribute but one with relation to visit and one to visit_action. The problem I have with this solution is that a attribute can end up being tied up to both visit and visit_action at the same time and I do not want that.

Option 2
Create columns with id_visit and id_visit_action directly in attribute table. It seems like a good fit because attribute can't be related to many visits or actions but there is still problem that it can end up being tied up to one visit and one action at the same time. It feels bad that too one column in attribute table will always be NULL (either id_visit or id_visit_action).


texts: SQL Server Transactional replication failed due to snapshot agent failure

texts:
I'm trying to set transactional replication between 2 SQL Server 2012 named instances. I've followed a step by step manual and created a Publication.
 But once I look into Replication Monitor I Get the following error:

2013-12-05 13:08:23.29 Copyright (c) 2008 Microsoft Corporation.
2013-12-05 13:08:23.29 The timestamps prepended to the output lines are expressed in terms of UTC time.
2013-12-05 13:08:23.29 User-specified agent parameter values:
2013-12-05 13:08:23.29 --------------------------------------
2013-12-05 13:08:23.29 -Publisher SRV1\SQL2012
2013-12-05 13:08:23.29 -PublisherDB DB_PUB
2013-12-05 13:08:23.29 -Publication DB_Pub
2013-12-05 13:08:23.29 -Distributor SRV1\SQL2012
2013-12-05 13:08:23.29 -DistributorSecurityMode 1
2013-12-05 13:08:23.29 -XJOBID 0xE8D827CC232A234FAC049095C03EBA66
2013-12-05 13:08:23.29 --------------------------------------
2013-12-05 13:08:23.29 Connecting to Distributor 'SRV1\SQL2012'
2013-12-05 13:08:23.35 The replication agent had encountered an exception.
2013-12-05 13:08:23.35 Source: Replication
2013-12-05 13:08:23.35 Exception Type: Microsoft.SqlServer.Replication.ConnectionFailureException
2013-12-05 13:08:23.35 Exception Message: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)

I wonder what may cause this type of error?


texts:
I have a MySQL 5.6.22 InnoDB database and just now, suddenly out of nowhere, when I try to start it up using /etc/init.d/mysqld start, I see this error in the error log and the server won't start up :

151204 19:08:17 mysqld_safe Starting mysqld daemon with databases from /u/mysql
2015-12-04 19:08:18 0 [Warning] options --log-slow-admin-statements, --log-queries-not-using-indexes and --log-slow-slave-statements have no effect if --slow-query-log is not set
2015-12-04 19:08:18 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-12-04 19:08:21 22922 [Note] Plugin 'FEDERATED' is disabled.
2015-12-04 19:08:21 7f469c74d720 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
2015-12-04 19:08:21 22922 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-12-04 19:08:21 22922 [Note] InnoDB: The InnoDB memory heap is disabled
2015-12-04 19:08:21 22922 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-12-04 19:08:21 22922 [Note] InnoDB: Memory barrier is not used
2015-12-04 19:08:21 22922 [Note] InnoDB: Compressed tables use zlib 1.2.3
2015-12-04 19:08:21 22922 [Note] InnoDB: Using Linux native AIO
2015-12-04 19:08:21 22922 [Note] InnoDB: Using CPU crc32 instructions
2015-12-04 19:08:21 22922 [Note] InnoDB: Initializing buffer pool, size = 2.0G
2015-12-04 19:08:21 22922 [Note] InnoDB: Completed initialization of buffer pool
2015-12-04 19:08:21 22922 [Note] InnoDB: Highest supported file format is Barracuda.
2015-12-04 19:08:21 22922 [Note] InnoDB: Log scan progressed past the checkpoint lsn 4231226488754
2015-12-04 19:08:21 22922 [Note] InnoDB: Database was not shutdown normally!
2015-12-04 19:08:21 22922 [Note] InnoDB: Starting crash recovery.
2015-12-04 19:08:21 22922 [Note] InnoDB: Reading tablespace information from the .ibd files...
2015-12-04 19:08:21 22922 [Note] InnoDB: Restoring possible half-written data pages
2015-12-04 19:08:21 22922 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Doing recovery: scanned up to log sequence number 4231231731200
InnoDB: Doing recovery: scanned up to log sequence number 4231235232564
2015-12-04 19:08:22 22922 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 03:08:22 UTC - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

key_buffer_size=209715200
read_buffer_size=131072
max_used_connections=0
max_threads=1024
thread_count=0
connection_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 612048 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x35)[0x8dc805]
7 /usr/sbin/mysqld(handle_fatal_signal+0x494)[0x6660e4]
/lib64/libpthread.so.0(+0xf790)[0x7f469c330790]
/usr/sbin/mysqld[0x999ecd]
/usr/sbin/mysqld[0x99bad5]
/usr/sbin/mysqld[0x984ace]
151204 19:08:22 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

Does anyone have any idea what could be wrong? It looks like there is an irreversible InnoDB corruption?

Thanks!


texts: Fetching Remote Encryption Key for MariaDB

texts:
I've set up encryption at rest for MariaDB but I can't seem to get my key fetching service to fetch the key.
I have the following fetch script and a systemd service set up.
# Fetch the encryption file for MariaDB, restart service, then remove file
/usr/bin/wget -P /etc/mysql https://example.com/my_maria_key.key
/bin/systemctl restart mysql
sleep 10
rm /etc/mysql/my_maria_key.key
touch /tmp/mariadb.started
exit 0

And my systemd service file:
[Unit]
After=network.service

[Service]
ExecStart=/etc/rc.local

[Install]
WantedBy=default.target

I made sure I enabled the service and I can run the service using:
sudo service fetch_key restart

But it doesn't fetch the key.
What have I done wrong?

ADDING .CNF FOR REFERENCE

[mysqld]
plugin_load_add=file_key_management
file_key_management = ON
file_key_management_filename = /etc/mysql/secretkeyname.enc
file_key_management_filekey = FILE:/etc/mysql/.key
file_key_management_encryption_algorithm=aes_cbc
encrypt_binlog = 1
innodb_encrypt_tables = ON
innodb_encrypt_log = ON
aria_encrypt_tables = ON
encrypt_tmp_disk_tables = ON
innodb_encryption_threads = 8
innodb_encryption_rotate_key_age = 0 # Do not rotate key

ADDING VARIOUS KEY GENERATION RESULTS

when using wget via sudo in the terminal:
644 -rw-r--r-- root root 620 mysecretkey.enc

letting the service download the key:
660 -rw-rw---- root root 620 mysecretkey.enc

generating the key with openssl:
644 -rw-r--r-- root root 336 mysecretkey.enc


texts: AlwaysOn Availability Group error after Windows Update in environment without a failover cluster

texts:
Our SQL Server service was down this morning which brought down some of our websites.  When I went to check the Windows Event Viewer I saw the following errors:

  Script level upgrade for database 'master' failed because upgrade step
  'SSIS_hotfix_install.sql' encountered error 942, state 4, severity 25

  Cannot recover the master database. SQL Server is unable to run.
  Restore master from a full backup, repair it, or rebuild it. For more
  information about how to rebuild the master database, see SQL Server
  Books Online.

The first thing that I did was to Google the errors.  I eventually found a forum entry with the exact problem and a fix for it (also on a blog entry where I am seeking a solution).  The problem has something to do with AlwaysOn Availability groups and the fix requires you to:

Start SQL Server service with Trace Flag 902:

  Net Start MSSQL$InstanceName /T902

Open SQL Server Management Studio, go to Availability Group and remove SSISDB from the availability databases
Open New Query, execute the SSIS_hotfix_install.sql script which can be found in Install folder under \Program Files\Microsoft SQL Server\MSSQL11.MSSQL$InstanceName\MSSQL
Stop SQL Server services:

  Net Stop MSSQL$InstanceName

Start SQL server service from SQL Server configuration manager
Add SSISDB back to Availability Group

However, I could not get past step #2 because I got the following error when I tried to expand the "AlwaysOn High Availability" folder:

  The "AlwaysOn feature must be enabled for the server instance
  'InstanceName' before you can create an availability group on this
  instance.

Then I followed the instructions to go to "SQL Server Configuration Manager" and the "AlwaysOn High Availability" tab to turn on the feature.  This time the feature was grayed out and there was a message saying that the computer node is not in a failover cluster.

My question is:

How can I fix this issue if we don't even have a failover cluster setup that would use this feature?

I ran dbcc checkdb on master; the results were:

  CHECKDB found 0 allocation errors and 0 consistency errors in database 'master'.

AlwaysOn Availability Group is NOT on because I do not even have a failover cluster.


texts: Working across multiple PostgreSQL database servers

texts:
So we are writing an app whose schema should reference data which lies currently in an external PostgreSQL instance. We are negotiating being able to put our schema within the external database, but we are evaluating different possibilities.

One option I'm pondering is basing our app on PostgreSQL and use its facilities for accessing external PostgreSQL instances.

What's the status of this?

PG's documentation contains http://www.postgresql.org/docs/9.1/static/sql-createserver.html and http://www.postgresql.org/docs/9.1/static/sql-createforeigntable.html , which allow you to reference tables in an external server. What's their status? Are queries performant (i.e. sends WHERE to the other side)? Can you reasonably join between local and foreign tables?

There's also pgsql_fdw ( http://interdbconnect.sourceforge.net/pgsql_fdw/pgsql_fdw-en.html ), which seems more featureful. Does it offer an improvement on the above? Anyone using it?

Thanks,

Alex


texts: Mirroring - Server network address cannot be reached

texts:
I have SQL Server 2008 R2 Installed. It contains three instances.

Default (MSSQLServer)
First Instance
Second Instance

All these are Log On as Network Service..

Default Instance is Principal server
First Instance is Mirror
Second Instance is Witness Server

I initially took the Full Backup and Transactional Log Backup of my Principal Database.
Restored it to the First Instance by keeping the same Database Name and Recovery State is No-Recovery

Finally, I started the Mirroring and I am getting two error messages shown below.


texts: Values of created_tmp_disk_tables increases

texts:
I noticed that the values of Created_tmp_disk_tables increased.
At a certain time, MYD files are created in /tmp directory and my site run very slowly.
I am forced to restart the mysql service to runing my site.

What is the cause of the creation of disk table in tmp directory and the stop of the mysql?

My server:

Ubuntu 12
RAM : 32 G
Processors Number: 12

my.cnf
key_buffer_size         = 1024M
max_allowed_packet      = 512M
thread_stack            = 256K
thread_cache_size       = 8
read_buffer_size        = 2M
join_buffer_size        = 2M
tmp_table_size          = 256M
max_heap_table_size     = 256M
bulk_insert_buffer_size = 256M
open-files-limit        = 2048
query_cache_type = 0
query_cache_limit       = 0
query_cache_size        = 0
innodb_file_per_table
innodb_table_locks              = true
innodb_lock_wait_timeout        = 60
innodb_thread_concurrency       = 8
innodb_commit_concurrency       = 2
innodb_support_xa               = true
innodb_buffer_pool_size = 16G
innodb_log_file_size    = 64M
innodb_additional_mem_pool_size = 16M
innodb_data_file_path   = ibdata1:10M:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size  = 64M
slow_query_log      = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
[myisamchk]
key_buffer                = 16M
myisam_sort_buffer_size   = 16M
myisam_max_sort_file_size = 2147483648
myisam_repair_threads     = 1
myisam-recover            = BACKUP

mysql> show global status like 'Created_tmp_%tables';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 25461 |
| Created_tmp_tables      | 29134 |
+-------------------------+-------+
2 rows in set (0.00 sec)

mysql> show global status like 'Created_tmp_%tables';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 25566 |
| Created_tmp_tables      | 29327 |
+-------------------------+-------+
2 rows in set (0.00 sec)

mysql> show global status like 'Created_tmp_%tables';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 25566 |
| Created_tmp_tables      | 29328 |
+-------------------------+-------+
2 rows in set (0.00 sec)

mysql> show global status like 'Created_tmp_%tables';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 25566 |
| Created_tmp_tables      | 29329 |
+-------------------------+-------+
2 rows in set (0.00 sec)

mysql> show global status like 'Created_tmp_%tables';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 25570 |
| Created_tmp_tables      | 29357 |
+-------------------------+-------+
2 rows in set (0.00 sec)


texts: Whether can mysql router connects to several MySQL InnoDB CLuster?

texts:
As suggested by MySQL, we can install MySQL Router service on application server.But how to configure the MySQL Router service when it needs to connect to several MySQL InnoDB Cluster?
For example, we have 2 InnoDB Clusters ClusterA and ClusterB.The applications connecting to the 2 clusters are under same host HostA.How can we configure in the mysql router on HostA, then we can use mysql router to connect 2 clusters.


texts: What is the difference between Federated and Decentralized Data Warehouse?

texts:
I can not find any clear definitions or explanations of any of these. Both seem decentralized. It appears that in Federated DWH, the data is distributed and not integrated into a single repository and accessed from distributed sources.

While in Decentralized DWH implementation the data is integrated into one central repository.

Please explain the difference between these two implementations.


texts: How secure is mongodb with elastic beanstalk and EC2

texts:
I have a questions.

I run MongoDB on one of instances of EC2 and application on Elastic BeansTalk.
I restricted access to DB: only application can access it.
DB instance has Elastic IP because it is replicated in different zones, so private IP is not an option.

Questions:

Can communication between application and DB be eavesdropped?
Should I implement SSL with mongo connection?

Thanks in advance to everyone!


texts: PostgreSQL - SELECT query with ORDER BY and LIMIT 4 very slow, same query much faster with LIMIT >= 40

texts:
I've got following sample query from our Postgres DB:

SELECT * FROM advertised_car a0_
LEFT JOIN car_check c1_ ON a0_.car_check_id = c1_.id
WHERE c1_.status  = 'ACCEPTED'
ORDER BY a0_.id
LIMIT 4;

The advertised_car table has about 2M rows, each advertised_car has at most 1 car_check.

The car_check_id is indexed on the advertised_car table. The primary keys and status as well.

This query is extremely slow - over 1s.
When I remove either the limit or order by clause, I get a result under 0.05s.

I suppose that since the results are filtered, the DB cannot utilize existing index on a0_.id and has to reorder all the results.

What I don't get is the fact that when calling this query:

SELECT * FROM advertised_car a0_
LEFT JOIN car_check c1_ ON a0_.car_check_id = c1_.id
WHERE c1_.status  = 'ACCEPTED'
ORDER BY a0_.id
LIMIT 40;

The query suddenly runs much faster, almost as fast as without LIMIT or ORDER BY clause.

The execution plans differ, but I don't understand why the index scan in the first plan takes so long

Full execution plans:

LIMIT 4:  https://pastebin.com/azvxT0ZY

LIMIT 40: https://pastebin.com/yABqQVCa

Visual:


texts: #1046: No database selected in phpMyAdmin

texts:
I have a problem with phpMyAdmin. When I click on any database on the left I get an error like:

SELECT tracking_active FROM.`pma_tracking`
WHERE db_name =  'information_schema'
AND table_name =  'CHARACTER_SETS'
ORDER BY version DESC

MySQL zwrócił komunikat: Dokumentacja

#1046 - No database selected

I can see database's tables if I click on '+' sign next to a database name and choose it but then I can't click on any other tab on the top of the page - I am getting the same error.

What can I do to make it work?


texts:
I am a newbie to MYSQL and I am trying to check where does MYSQL log start and stop entry with timings. I have issue in my application where I get communication link failure at random intervals and trying to check whether MYSQL was down at that time and it restarted after few minutes or not.

MYSQL installation details:

innodb_version 5.6.15
protocol_version 10
slave_type_conversions
version 5.6.15-enterprise-commercial-advanced
version_comment MySQL Enterprise Server - Advanced Edition (Commercial)
version_compile_machine x86_64
version_compile_os Linux

any help would be appreciated.


texts: Performance for PostgreSQL with datafile mounted on NAS

texts:
I'm interested is there someone tried to run PostgreSQL server with datafile mounted on NAS server via iSCSI?

Are there any performance issues? Can you share some information?


texts: MongoDB replCallbackWithGlobalLock-0. What does it mean?

texts:
I have a production replica set with 3 members (primary, secondary, arbiter). Almost every day, there's a short downtime where the primary is demoted to secondary for no apparent reason.

The replica set log from the primary says this:

  "2016-01-18T21:32:07.281-0500 I REPL     [ReplicationExecutor] Member 2.2.2.2 is now in state PRIMARY",
  "2016-01-18T21:32:07.282-0500 I REPL     [replCallbackWithGlobalLock-0] transition to SECONDARY",
  "2016-01-18T21:32:25.264-0500 I REPL     [ReplicationExecutor] Member 3.3.3.3 is now in state ARBITER",
  "2016-01-18T21:32:38.520-0500 I REPL     [ReplicationExecutor] Member 2.2.2.2 is now in state SECONDARY"
  "2016-01-18T21:32:41.688-0500 I REPL     [ReplicationExecutor] transition to PRIMARY",
  "2016-01-18T21:32:42.341-0500 I REPL     [rsSync] transition to primary complete; database writes are now permitted",

2.2.2.2 is the secondary. As you can see, the secondary takes over, but I have no idea why. Usually when this happens, the application is unable to connect to the database for a few seconds.

Any explanation will be greatly appreciated.


texts: As there is no unsigned int in SQL Server doesn't an Identity Seed of -2,147,483,648 make more sense for large tables?

texts:
I just had a showerthought that the default Identity Seed is 1. I have some tables that I know will grow to the billions at a certain point. Wouldn't it make more sense to start on int.Min ( -2,147,483,648) for these tables?
This could just make the difference of migrating your key to bigint in 4 years or in 8 years. Can be relevant enough.
Is this common? It feels weird. Is there anything I am missing?


texts:
I have a serially modified table of processed events.
A utc time for when it was processed and an eventId.

Is it safe to use SYSUTCDATETIME() as my primary key? I can easily use a surrogate key but it felt funny.


texts: "TRUNCATE doesn't run ON DELETE trigger" true or false

texts:
I was searching on the topic of why TRUNCATE is placed in DDL instead of DML for which I found following answer here
Why is truncate DDL?

I this answer I found a sentence as given below

"The fact that TRUNCATE doesn't run ON DELETE triggers also sets it apart from normal DML operations"

This conflicts with MYSQL Reference Manual which says

"If the FOREIGN KEY constraint specifies DELETE CASCADE, rows from the child (referenced) table are deleted, and the truncated table becomes empty."

I am in a confused state. Please elaborate on this issue.

Thank you


texts: Recently MongoDB added hash index to its features but in the documentation doesn't explain much about hash index use case and its privileges to normal MongoDB index. So I'll be grateful if you guide me to know more about it.


texts: Columns names are case sensitive on postgres

texts:
I have successfully migrated database from MySQL to PostgreSQL. While I am trying to connect with ofbiz, I am getting an error saying that

  "user_id" not found.

When I query against postgres by using

select * from user where user_id='1';

It's throwing the same error. But when I try with

select * from user where "USER_ID"='1';

It's fetching correctly. Table names are not case sensitive here, only column names. Can anyone give me a solution please?


texts: MySql Replication Failure on primary secondary

texts:
We have experienced replication failure between primary - secondary databases.

I have tried various scenarios to get replication to work again, which included deleting secondary database and restoring it from primary backup. However, replication still fails.

Would it be best to follow steps:

On Slave

mysql> STOP SLAVE;

On Master

mysql> RESET MASTER;
mysql> FLUSH TABLES WITH READ LOCK;
mysqldump -u root -p mydb > mydb-dump.sql
mysql> UNLOCK TABLES;

On Slave

mysql -u root -p mydb < mydb-dump.sql
mysql> RESET SLAVE;
mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1;
mysql> START SLAVE;
mysql> show slave status\G

Is it safe to execute

mysql> RESET MASTER;
mysql> RESET SLAVE;

as those commands deletes the binary log files. Or would it be better to use

mysql> PURGE BINARY LOGS

Any thoughts would be greatly appreciated.

Cheers,
Roland


texts:
Hi I am looking to set up my report so the start date will always be say '14-09-2011 00:00:00'
And the end date will always be '14-09-2011 23:59:59' Or in other words Start date will be 00:00 and end date will be 23:59 for what ever days I select.

At the moment my parameters are

Startdate: =DateAdd(DateInterval.Day, -7, Today())

End Date: =today()


texts: table size seems high for the amount of records in db

texts:
I have a simple table

and I have blanked out the twitterdetail and rawsource and set it as null so there is no data in them, however the space for that table is 350mb.

There is 67k records so if one does math on the filled fields, it should not be that high.

Am I missing something?


texts:
I have the following requirements:

Every transaction has one of the following types; debit, credit, deposit, or withdraw.
Debit or credit transactions must have a linked invoice record and no bank account record.
Deposit or withdraw transactions must have a linked bank account record and no invoice record.

Currently my basic design is like this:

My current solutions are:

Have atwo nullable foreign keys to the invoice and bank account tables in the transaction table. However, I believe a nullable foreign key column isn't a good design.
Have a foreign key in the invoice and bank account tables that link to the transaction table. However it's seems harder to query and it models a one-to-many relation (not one-to-optional-one relation I want).

Which approach that I outlined is a better solution? Or is there another way to meet my constraints that is even better?


texts: Do PostgreSQL toasted fields use any per row, in-table space?

texts: I understand that PostgreSQL will store large fields in a separate storage space automatically. When this occurs, is there anything stored per row for that field in the table? A reference or hint of some kind?


texts: Is there any way where I can get hostname/servername in postgresql like @@ServerName in SQL Server?


texts: ORA-01658 on index tablespace when trying to insert into empty table of new database

texts:
We are getting ORA-01658 (Error :ORA-01658: unable to create INITIAL extent for segment in tablespace MYDATA_LDATA_INDEX) in our application logs. This happens when application is trying to insert data for the first time into tables that are empty. It is only happening to this particular index. The tablespace free space decreases by multiple of 50M.

The parameters used to create the index tablespace:

CREATE TABLESPACE MYDATA_LDATA_INDEX DATAFILE
  '/index/oradata/mydb/mydata_ldata_index_01.dbf'
SIZE 1300M AUTOEXTEND ON NEXT 10M MAXSIZE 2048M
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 50M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

We are sure application is not trying to insert massive amount of data into the tables, an operation that only insert one row also causes this.

After increasing tablespace for the index manually, regular operation can be continued and tablespace usage for the index is not increasing dramatically.

We are using Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production on Solaris 11.1.

How do we pin point source of the problem? Is there an issue with the parameter of the index tablespace?


texts: Full and differential restore with two backup files - SQL Server 2008

texts:
We are trying to establish a maintenance plan with 2 separate backups.

One full backup over the weekend and differential backups through the week. [the differential backup has a "_diff" appended to its name].

The backups are supplied to us by an external entity we use for processing orders.  My problem is with restoring the databases.

1: I tried the method here: http://msdn.microsoft.com/en-us/library/ms175510.aspx#TsqlProcedure but, I can't seem to "add" a backup file to the existing backup device on the server that contains the full backup.

2: I tried restoring them as 2 separate backups but I get this error:

The backup set holds a backup of a database other than the existing 'DBXXX' database. [possibly due to the _diff name associated with the .mdf and .ldf files]

How do I go about restoring the differential backups in this scenario.  Can I add the differential backup to the existing full backup device?  Is there another way out?

Edit:

Added T-SQL statements:

Restoring the full backup

RESTORE DATABASE DB_TEST FROM DB_TEST_Device WITH REPLACE, NORECOVERY;
GO

Restoring the diff. backup

RESTORE DATABASE DB_TEST FROM DB_TEST_Device_Delta WITH RECOVERY;
GO

**Full Backup Device:**
OM_00987_Backup_987 D:\SQLdata\OM_00987_Backup_987.mdf  D   PRIMARY 32852082688 3.51844E+13 1   0   0   BAC0AD62-9CB6-46FA-8600-7852009C638A    0   0   32850247680 512 1   NULL    1.33E+17    2B366F37-7D81-4BD1-AA3C-DD9757EFF075    0   1   NULL

OM_00987_Backup_987_log    E:\SQLlogs\OM_00987_Backup_987_log.LDF  L   NULL    516096  2.19902E+12 2   0   0   F76787F2-521B-4A75-A2F6-8977DB88E0A9    0   0   0   512 0   NULL    0   00000000-0000-0000-0000-000000000000    0   1   NULL

**Delta Backup Device:**
OM_00987_Backup_987_Delta   D:\SQLdata\OM_00987_Backup_987_Delta.mdf    D   PRIMARY 9633464320  3.51844E+13 1   0   0   E75C765B-ECEC-46AC-B8AE-5F1EB7D3C724    0   0   9629138944  512 1   NULL    1.36E+17    8C66A6C8-DE57-4B57-B0E1-DE8F3C64E68A    0   1   NULL

OM_00987_Backup_987_Delta_log  E:\SQLlogs\OM_00987_Backup_987_Delta_log.LDF    L   NULL    516096  2.19902E+12 2   0   0   FC843B68-BC7C-478A-BE4E-D4AA4D61B30E    0   0   0   512 0   NULL    0   00000000-0000-0000-0000-000000000000    0   1   NULL


texts: Oracle 19c with SAP System - ORA-12154: TNS:could not resolve the connect identifier specified For Physical Standby

texts:
Oracle 19c + SAP system. On primary side I have a problem when transporting logs to Data Guard side.
Outputs from primary side:
SQL> select status, error from v$archive_dest where dest_id=2;

STATUS    ERROR
--------- -----------------------------------------------------------------
ERROR     ORA-12154: TNS:could not resolve the connect identifier specified

SQL> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      SERVICE=DRSIDE NOAFFIRM ASYNC V
                                                 ALID_FOR=(ONLINE_LOGFILES,PRIM
                                                 ARY_ROLE) DB_UNIQUE_NAME=DRSIDE

SQL> show parameter log_archive_dest_state_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2             string      ENABLE

Alert log:

TT00 (PID: a number): Error 12154 received logging on to the standby
2021-02-20T15:48:51.144500+03:00
TT00 (PID: a number): Error 12154 received logging on to the standby
2021-02-20T15:53:51.946941+03:00
TT00 (PID: a number): Error 12154 received logging on to the standby
2021-02-20T15:58:52.744327+03:00
TT00 (PID: a number): Error 12154 received logging on to the standby
2021-02-20T16:03:53.549161+03:00
TT00 (PID: a number): Error 12154 received logging on to the standby

I did not make any changes to the tnsnames.ora or listener.ora files.
Edit: I tried to set log_archive_dest_2, I tried to send tnsping (successfully works from primary to disaster and disaster to primary), I tried copying passwordfile from primary to disaster. None of them worked.
Best regards,


texts: Can a SELECT with SELECT Subquery cause deadlock with Update query in SQL Server?

texts:
I am using entity framework with table per type. I have a table called ins_GameAppInstances that has a discriminator column for the type of object I am returning. There is another table that has some settings that have a foreign key to ins_GameAppInstances called ins_DefaultBankAccountInstances.

When I have high throughput to the database a deadlock is happening between a select with a select subquery and an update to the same ins_GameAppInstance table. The sql server is choosing to kill the select of course as a victim.

I am wracking my brain to this point. If you look at the page locks, there is Page ID 6122 and then Page ID 4537. Could this be happening because the first select locks on S, the update gets inbetween the subquery select and locks on IX and then the subquery locks a later page? This causes the deadlock? It's really the subquery select that is causing this mess?

I know that the update is not updating anything to do with the select query results. Would it be safe to just allow READ_COMMITTED in SQL Server. I don't really want to do that but I also don't want to change the way my domain logic works because of Entity Framework and entity data mapping.

UPDATE:
I was thinking that as a last resort I could just deal with it by grabbing in a try catch sql exception. Throw the exception up the stack. When I run my integration test with a console app just smashing the db there is significant wait though for the deadlock to happen.

catch (SqlException ex)
        {
            if (ex.Number == 1205)
            {
                if (LogManager.Enabled)
                    LogManager.GetLogger(GetType()).Debug("Deadlock");
            }
            else
                throw;
        }

Here is the ins_GameAppInstance table

Here is the DefaultBankAccountInstance table. The GameAppInstanceID is the foreign key to ins_GameAppInstances.

Here is the deadlock that is happening. Request and owner S on the left and IX Request and owner on the right.

Here is the select and select subquery that entity framework is building:

exec sp_executesql N'SELECT
[Join1].[CurrentWeek] AS [CurrentWeek],
[Join1].[Discriminator] AS [Discriminator],
[Join1].[ID1] AS [ID],
[Join1].[Name] AS [Name],
[Join1].[WeekHasStarted] AS [WeekHasStarted],
[Join1].[Created1] AS [Created],
[Join1].[GameAppID] AS [GameAppID],
[Join1].[GameInstanceID] AS [GameInstanceID],
[Join1].[ID2] AS [ID1]
FROM  [dbo].[ins_GameAppInstances] AS [Extent1]
INNER JOIN  (SELECT [Extent2].[ID] AS [ID1], [Extent2].[Name] AS [Name], [Extent2].[CurrentWeek] AS [CurrentWeek], [Extent2].[WeekHasStarted] AS [WeekHasStarted], [Extent2].[Created] AS [Created1], [Extent2].[Discriminator] AS [Discriminator], [Extent2].[GameAppID] AS [GameAppID], [Extent2].[GameInstanceID] AS [GameInstanceID], [Extent3].[ID] AS [ID2]
    FROM  [dbo].[ins_GameAppInstances] AS [Extent2]
    LEFT OUTER JOIN [dbo].[ins_DefaultBankAccountInstances] AS [Extent3] ON [Extent2].[ID] = [Extent3].[GameAppInstanceID] ) AS [Join1] ON [Extent1].[ID] = [Join1].[ID1]
WHERE ([Extent1].[GameInstanceID] = @EntityKeyValue1) AND ([Join1].[Discriminator] IN (N''BankingAppInstance'',N''BillsAppInstance'',N''CashboxAppInstance'',N''CreditCardsAppInstance'',N''EmailsAppInstance'',N''ExpensesAppInstance'',N''RandomEventsAppInstance'',N''TextMessagesAppInstance'',N''GameAppInstance''))',N'@EntityKeyValue1 uniqueidentifier',@EntityKeyValue1='55E2A02B-73AF-4CC9-BC09-7BD46473CF4F'

Here is the update that is being sent at the same time:

    exec sp_executesql N'UPDATE [dbo].[ins_GameAppInstances]
SET [CurrentWeek] = @0, [WeekHasStarted] = @1
WHERE ([ID] = @2)
',N'@0 int,@1 bit,@2 uniqueidentifier',@0=4,@1=0,@2='FD00CB51-A8DD-4705-87E9-B1DD34322264'

UPDATE
XML DEADLOCK FILE

http://pastebin.com/mRNm0Rje


texts: Blocking continues after enabling snapshot isolation

texts:
On database hosted on a SQL Server 2012 instance, I have enabled ALLOW_SNAPSHOT_ISOLATION and verified the state as ON using

SELECT snapshot_isolation_state_desc,name from sys.databases

However, in 2 separate sessions, if I run a long running select with TABLOCK in the 1st and an UPDATE in the 2nd (or vice versa), whichever query starts first blocks the second query (as per sp_who2)

Looking at

select * from sys.dm_exec_requests , both queries have a transaction isolation level of read committed (2)

As per my understanding, with snapshot isolation on, tempdb usage should increase however blocking should not occur in this situation. Am I missing some configuration steps to achieve this behaviour?


texts: Can a primary key of one table be a foreign key of another table?

texts:
I am working on a scenario like this :
I have an employee table which has columns like this

Employee_ID
First_Name
Last_Name
Employee_Designation

Also I have a Joining_Vital table

Employee_ID
Joining_ID
Joining_Date

How can I link this two tables ?
As I guess it would be a many-to-one relationship(as one joining_vital can contain many employees) from Employee table to  Joining_Vital table

I cannot establish the foreign key on the Employee_ID column of the Employee table.
I am naive to Database thing
Could you guys please help me out how to do it?
I cannot figure out the scenario
Please explain me out. Am I missing anything here?


texts: Database Cache Memory in Performance Monitor drops down significantly after DBCC CheckDB

texts:
We have been monitoring some SQLServer: Memory Manager's metrics, and noticed that after DBCC CheckDB job, metric

Database Cache Memory (KB)

drops down significantly. If to be exact, it dropped from 140 GB cached DB memory to 60 GB. And after that, slowly ramp up again during the week. (Amount of "Free Memory KB", went from 20 to 100 GB right after CheckDB)

DBCC CheckDB is run every Sunday, so Database Cache Memory has to ramp up back again each week

What is the behavior of this ? Why CheckDB pushes database pages out of memory ?

Second question is why "buffer cache hit ratio" did not change after DBCC CheckDB completes ?

It was 99.99% on average and after DBCC CheckDB job it drops to ~98.00%, and returns back to 99% pretty fast while I expected "buffer cache hit ratio" to drop significantly because database data has to be read from storage to RAM again ?


texts:
Even though split brain scenario occurs in both Oracle RAC and Percona's XtraDB Cluster, a two node cluster is allowed and split brain scenario is resolved in RAC but a two node is not recommended in Percona Cluster ( 3 nodes is recommended ). Why is it like that? Doesn't Percona resolve split brain happening in two node cluster? I know I'm comparing two different technologies.. but still if anyone could clear this out.

Thanks!


texts: Database file paths for Named Instances in AAG's

texts: I have configured a 2 node AAG setup with named instances say N1 and N2. During the AAG creation and database seeding from N1 , I got an error saying that N2 did not have any folders/path similar to N1 and I could not continue. So I created another Directory path for the database files with the same name as that on Node 1. Will this work? Am I missing anything else? Please clarify


texts: Is COUNT(DISTINCT PRIMARY_KEY) faster than COUNT(*)?

texts: I haven't got a particular problem here, I'm just interested. One of my associates uses COUNT(DISTINCT(PRIMARY_KEY)) every time he needs to select all the data from a table (with 100m + rows) is it any quicker to do it this way rather than just count(*)? If it's the primary key surely the database knows that it's just pulling a count for the whole database and why?


texts: Teach MySQL cli autocomplete to only list table names, not field names?

texts:
When starting to write a MySQL query, e.g. describe myt and hitting TAB, MySQL autocomplete will list not just the table names starting with myt (e.g. mytable), but all fields/columns of each matching table. E.g. mytable.id, mytable.name, etc.

This can easily result in Display all 505 possibilities? (y or n), and makes the autocomplete less useful than it could be.

Is there a setting or a trick to let MySQL (or MariaDB) only show the table names, not the field names? Or in other words, to treat the dot in the same way that regular shell/bash autocomplete treats the directory separator?


texts: MySQL slow query log not logging all slow queries

texts:
I am trying to analyze the hibernate query performance in our application. I tried logging all hibernate queries which take more than 100ms execution time from application side as well as using mysql slow query log. I set the long_query_time = 0.10 in my.cnf.

Now my problem is some of the insert queries which are consistently taking more than 100ms from the application log are getting logged only few times in mysql sloq query log. Also some insert and select statements are not getting logged at all; but when I try running all these statements from SQLYog, they are taking more than 100ms.

Why mysql sloq query log is not logging these statements?


texts: Add additional disks to FRA diskgroup in ASM

texts:
We're running Oracle 11.2.0.3 with a two node RAC and our FRA diskgroup is running low on space.  I need to allocate some more disks to it through ASM.
I'm not sure how to go about doing this.
I can login to a node and get to asmcmd or other asm command line utilities.  What do I need to do?

EDIT:  Didn't know it was relevant till Phil's answer: we're using raw disk devices.


texts: Offline index rebuild - does it also create a whole new copy of the index?

texts:
As I know, while online index is rebuilding, SQL Server virtually creates an index and then at the end it swaps the existing old index with the new created one and then finally drops the old index.

I would like to know if these operation are the same for offline index rebuild. Will SQL Server create virtually an index and then swap it with the existing one?


texts: How can I view special characters in SQL Server 2012 Management Studio grid view?

texts:
In a SQL Server 2012 database, I have a log table where I log SQL queries executed by a VB.NET application.

The field is defined as nvarchar(MAX)

In Microsoft SQL Server Management Studio, when I go to the table and do "Edit All Rows",
on one line I see spaces in the end of the text

But when I enter my cursor in the line to edit it (not touching anything, before I even start editing), the spaces disappear (along with the trailing quote)

And when I go to New Query and execute the same query

SELECT LOGID, LogDate, SqlLog
FROM ezber_SQL_LOG
WHERE (LOGID = 1604))

I get the result without the spaces and the trailing quote:

So my guess is that there are special characters in the data (line breaks, tabs or something like that), but how can I find out for sure and know what they are?


texts: Which error codes are safe for `slave_skip_errors` with MySQL Innodb

texts:
Does anyone know which error codes are safe to ignore during replication, meaning the slave will remain consistent with the master?

For example

[mysqld]
slave-skip-errors=1007,1008,1050,1051,1054,1060,1061,1068,1094,1146

The example that I found omitted is 1213 (Deadlock found when trying to get lock; try restarting transaction, Error_code: 1213; handler error HA_ERR_LOCK_DEADLOCK), which seems safe to me?

I've found little advice on the matter so seems like a good one for stackoverflow...


texts: Make to_tsquery return OR of lexemes instead of AND

texts:
I'm using an dict_xsyn extension in postgres which returns multiple lexemes.

e.g.

CREATE EXTENSION dict_xsyn;

CREATE TEXT SEARCH DICTIONARY names_xsyn (TEMPLATE = 'xsyn_template');

CREATE SEARCH CONFIGURATION english_names ALTER MAPPING FOR asciiword, asciihword, hword_asciipart WITH names_xsyn, simple;

SELECT to_tsquery('english_names', 'ed')
-- returns: 'ed' & 'edward' & 'edwin' & 'edmond'

I would prefer if to_tsquery returned ORed lexemes so that I could match to other variants. e.g.

-- returns: 'ed' | 'edward' | 'edwin' | 'edmond'

I've tried ts_rewrite but that doesn't seem to support swapping logical operators.

--Edit
I'm doing this so that I can match partially matching sets.
e.g.
I want ed to match edward and edwin, but I don't want edward to match edwin. Here are my lexemes:

ed -> ed, edward, edwin

edward -> ed, edward

edwin -> ed, edwin

Using those lexmes to match ed to edward will only work with an or.

"ed, edward" @@ "ed | edward | edwin" will work

"ed, edward" @@ "ed & edward & edwin" won't work


texts: Merging Results while avoiding Key Constraint Error?

texts:
I have a table with a primary key which is two integer fields, each a foreign key to a different table. (Multi to Multi table)

In one of the foreign tables two rows are merged and this table is updated to match the changes which can result in two rows being duplicates and cause the query to fail.

There has to be a better way to do the following: (which does work)

SELECT row1, row2
FROM table
WHERE row2 = @ID OR FK_Gmb_row2 = @ID2 GROUP BY row1, row2;

DELETE FROM table WHERE row1 = @ID OR row2 = @ID2;

-- process select in code and loop over results::
INSERT INTO table (row1, row2) VALUES (@val1, @val2)

So if FK_row had the values 2 and 3 merged Table 1 would go from

FK_row1 , FK_row2
-----------------
1       , 1
1       , 2
1       , 3
2       , 3

To

FK_row1 , FK_row2
-----------------
1       , 1
1       , 3
2       , 3

I feel like I am using this solution because I do not properly understand something about the database system.


texts: If PK is Non-Clustered index, how to identify clustered index candidate?

texts:
I'm designing a database for insurance company. I want to make Primary key as GUID-uniqueidentifier. I want to create Index for each table in database for performance optimization.

I did research and i came to know that GUID primary key should be Non Clustered always. I agreed that clustered index on GUID will create fragmentation in memory.

I have sample tables,

CREATE TABLE [dbo].[ProductState](

    [ProductStateId] [uniqueidentifier] NOT NULL,
    [ProductId] [uniqueidentifier] NOT NULL,  -- Foreign key
    [StateId] [uniqueidentifier] NOT NULL, -- Foreign key
    [CreatedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_ProductState] PRIMARY KEY NONCLUSTERED
(
    [ProductStateId] ASC
)

This is a mapping table between two master table in which only foreign key columns are available and which are again GUIDs.

Another sample table,

CREATE TABLE [dbo].ProductCompare(
    [ProductCompareId] [uniqueidentifier] NOT NULL,
    [ProductRateId] [uniqueidentifier] NOT NULL,          -- Foreign key
    [ProductId] [uniqueidentifier] NOT NULL,              -- Foreign key
    [Program] [int] NOT NULL,
    [MarkUpValue] [decimal](19, 10) NULL,
    [IsSelected] [bit] NOT NULL,
    [ComparisonValue] [varchar](max) NULL,
    [ComparisonOrder] [int] NOT NULL,
    [CreatedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_ProductCompare] PRIMARY KEY NONCLUSTERED
(
    [ProductCompareId] ASC
)

This is child table which has reference of master table along with another column, but i don't see any column here which can work as Clustered Index key.

In my entire database there are many scenarios of this kind where i don't have any column which can work as Clustered Index key.

I need suggestion how should i manage Indexing for database of insurance company web application.


texts: Installing SQL Server, I get unmet dependencies libc++1 and libjemalloc1 but it is not installable

texts:
Here is the output,

$ sudo apt install -y mssql-server
Reading package lists... Done
Building dependency tree
Reading state information... Done
Some packages could not be installed. This may mean that you have
requested an impossible situation or if you are using the unstable
distribution that some required packages have not yet been created
or been moved out of Incoming.
The following information may help to resolve the situation:

The following packages have unmet dependencies:
 mssql-server : Depends: libjemalloc1 but it is not installable
                Depends: libc++1 but it is not installable


texts: Multiple match() against() in a MySQL query

texts:
Same question asked on here, with no answers.

I'm using MySQL 5.7.17 on Windows 10.
I got this table:

CREATE TABLE `tbl1`(
  `id` int(10) unsigned NOT NULL,
  `col1` varchar(128) NOT NULL DEFAULT '',
  `col2` varchar(128) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  FULLTEXT KEY `col1_idx` (`col1`) WITH PARSER `ngram`,
  FULLTEXT KEY `col2_idx` (`col2`) WITH PARSER `ngram`,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And I would like to make a query that col1 MUST have str1 and col2 MUST NOT have str2, I run multiple MATCH() AGAINST() in a query,like this

SELECT * FROM tbl1
 WHERE MATCH(col1) AGAINST('+str1' IN BOOLEAN MODE)
   AND MATCH(col2) AGAINST('-str2' IN BOOLEAN MODE);

But this query returns nothing.

When I use EXPLAIN, like:

EXPLAIN SELECT * FROM tbl1 WHERE
 MATCH(col1) AGAINST('+str1' IN BOOLEAN MODE)
   AND MATCH(col2) AGAINST('-str2' IN BOOLEAN MODE);

it returns:

+----+-------------+-----------+------------+----------+-------------------------+---------------+---------+-------+------+----------+-----------------------------------+
| id | select_type | table     | partitions | type     | possible_keys           | key           | key_len | ref   | rows | filtered | Extra                             |
+----+-------------+-----------+------------+----------+-------------------------+---------------+---------+-------+------+----------+-----------------------------------+
|  1 | SIMPLE      | tbl1      | NULL       | fulltext |    col1_idx, col2_idx   | col1_idx      | 0       | const |    1 |    11.11 | Using where; Ft_hints: no_ranking |
+----+-------------+-----------+------------+----------+-------------------------+---------------+---------+-------+------+----------+-----------------------------------+

How do i make the query works? Any advice would be appreciated.


texts: How to make iSQL/FreeTDS behave like a normal client?

texts:
I have a third-party Sybase database, which my users would like to connect to and query from a Linux box, using iSQL. I have installed UnixODBC and FreeTDS. As it is third-party database however, I am not able to perform any GRANTs. The query fails with iSQL on Linux:

[42501][unixODBC][FreeTDS][SQL Server]ASA Error -121: Permission denied: you do not have permission to use the "CREATE PROCEDURE" statement

It appears that the actual SQL that is getting sent by iSQL is CREATE PROCEDURE <some temporary name> AS <the actual sql>. I have read the iSQL and FreeTDS manpages but there is no hint in there about how to disable this behavior.

This works OK in Python:

>>> import Sybase
>>> db = Sybase.connect('host:port', 'user', 'password', 'db')
>>> c = db.cursor()
>>> c.execute('select table_name from systable')
>>> len(c.fetchall())
570

So I believe I have installed the Sybase client libraries correctly. Before I recommend this solution instead, can I as a DBA do anything about fixing iSQL?


texts: Java-Hibernate-Oracle: Can a fluctuating DB connection duplicate insert statement?

texts:
  Is it possible that a fluctuating DB connection causes insert statement to execute multiple times?
Thanks


texts: How do get correct instance name on Multi instance cluster

texts:
Just a quick question and somehow unable to figure it out.
We have a Active Active cluster or should i say Multi instance cluster as below. node A and B are part of same window cluster

Node A --> Serv1\SQLINSt1
Node B --> Serv2\SQLINSt2

How can i show the info that node A at a time host instance Serv1\SQLINSt1 and node B other instance Serv2\SQLINSt2 because when i use dmv sys.dm_os_cluster nodes and add SERVERPROPERTY ('instancename') it gives same SQL instance for both the nodes
Also will it be possible to show when both the instances come on says node A while node B host no instance?
Much appreciated thanks


texts: Yet Another error 10054 after disabling TLS 1.0

texts:
We've got:

SQL Server 2012 SP4-OD 11.0.7469.6 running on
Windows server 2012 R2 Build 9600 with
.NET Framework 3.5.30729.4926 installed, while
SPN registers perfectly at SQL Server startup.

That looks like it meets all the requirements in https://support.microsoft.com/en-us/help/3135244/kb3135244-tls-1-2-support-for-microsoft-sql-server
What am I missing?


texts: Scheduling queries to run when sql server is not busy

texts:
Is it possible to schedule some queries not for specific times but to run when sql server is not busy?

Is there any way to calculate the business of the sql server?


texts: Can't get PostGIS to show points on map - no button to activate map?

texts:
PostgreSQL 12 and PostGIS 3.0.1 on Windows 10, using PGAdmin4. I'm totally new to PGAdmin4 having recently come from PostgreSQL 9 / PGAdmin III - frankly, I don't like it but I'm trying.

For the first time, I'm attempting to use PostGIS to calculate point-to-point distance from a list of a few hundred points (eventually up to 1500). I see there is a feature to allow a visual map of the points based on their location, but the functionality isn't showing on my installation - the Mapping icon (blue, I think) isn't there on the column display as I've seen on the 'net.

I've gone thorough everything I can find and I can't seem to figure out what I've done wrong. I've verified PostGIS is enabled and re-created the column in a couple different ways with no change. I re-set the geometry field because I learned that longitude comes first in Postgis (odd in my view, but I'm sure there's a reason) but that didn't help either. I learned that using "4326" SRID is required so I've done that in a couple different ways also. My database/schema has the required "spatial_ref_sys" table in it. I don't know what else to check.

select * from pg_extension gives me;

  oid  |  extname  |  extowner  |  extnamespace  |  extrelocatable  |  extversion  |  extconfig  |  extcondition
"13304"  "plpgsql"      "10"           "11"             false             "1.0"         [null]         [null]
"19086"  "postgis"      "10"        "16460"             true            "3.0.1"        "{19391}"   "{"WHERE NOT (
"20595" "adminpack"     "10"           "11"             false             "2.0"         [null]         [null]

The "WHERE NOT (" part followed by 500+ lines listing all the SRID codes.

This command I found on the web does not work at all:

SELECT AddGeometryColumn('example', 'geom', 4326, 'POINT', 2)

and results in:

ERROR:  function public.addgeometrycolumn(unknown, unknown, character varying, character varying, integer, character varying, integer, boolean) does not exist
LINE 1: SELECT public.AddGeometryColumn('','',$1,$2,$3,$4,$5, $6)

I can't seem to get it to point to my schema no matter what I do. So I used these commands to add my column and update the geometry field (lon_dec and lat_dec contain long/lat in decimal degrees):

ALTER TABLE kmsy.ground_points ADD COLUMN geom kmsy.geometry(Point,4326)

UPDATE ground_points SET geom = ST_SetSRID(ST_MakePoint(lon_dec, lat_dec), 4326)

but the SRID constraint is not appearing in the table list.I know I've done something basic wrong, I just can't find it.


texts: Oracle data transfer rate determined by rows instead of data size

texts:
Is there a setting in Oracle 11g that can throttle the rows/second being pulled from the db?

Our data loads from Oracle are really slow. It does not seem to matter whether its OPENQUERY, SSIS, or SQL Developer. I ran some tests and found that as the row number increases, the time to transfer increases in a linear fashion (100K rows in 90 seconds, 1M rows in 903 seconds). I then ran another test where the row count remained the same (500K) but the amount of data in each row increased with each trial. For this test, the time was almost flat (~480s), even though the amount of data increased from 5MB to 45MB over 10 trials.

The issue does not seem to be a throughput (VPN tunnel) problem since data speed increased with data/row.

So, my question is, what in Oracle would be causing this consistent 1000 rows/sec value, regardless of the amount of data in each row?

We don't manage the Oracle db, but I'm hoping to go back to the dbas with some ideas to speed up our month end loads.

Test 1: Increase Row Count, Maintain Data/Row

Test 2: Maintain Row Count, Increase Data Per Row


texts:
How do I name the columns of a PIVOT table to something that is not a number?

Is it a requirement that the names of the spreading columns be a number?

I'm working my way thought the book Querying Microsoft SQL Server 2012 in preparation for the 70-461 exam. I am currently on the lesson for PIVOT tables (Chapter 5, Lesson 2, Page 163). The following SQL executes successfully.

WITH Pivot234
AS
(
select  ss.companyname,                         --Grouping column
        datepart(dw, so.orderdate) as DayNumber, --Spreading column
        so.freight                              --Aggregation Column
FROM Sales.Orders so
LEFT OUTER JOIN sales.Shippers ss ON so.shipperid = ss.shipperid
)

SELECT  companyname, [1],[2],[3],[4],[5],[6],[7]
FROM Pivot234
PIVOT (AVG(FREIGHT)
       FOR DayNumber IN ([1],[2],[3],[4],[5],[6],[7])
       ) AS p;

I want the name of my attributes to be the days of the week not numbers. When I change the numbers in the PIVOT table operator to the days of the week I get the following...

WITH Pivot234
AS
(
select  ss.companyname,                         --Grouping column
        datepart(dw, so.orderdate) as DayNumber, --Spreading column
        so.freight                              --Aggregation Column
FROM Sales.Orders so
LEFT OUTER JOIN sales.Shippers ss ON so.shipperid = ss.shipperid
)

SELECT  companyname, [Mon],[Tues],[Wed],[Thurs],[Fri],[Sat],[Sun]
FROM Pivot234
PIVOT (AVG(FREIGHT)
       FOR DayNumber IN ([Mon],[Tues],[Wed],[Thurs],[Fri],[Sat],[Sun])
       ) AS p;

The second SQL query has the following error...

  Msg 8114, Level 16, State 1, Line 36
  Error converting data type nvarchar to int.
  Msg 473, Level 16, State 1, Line 36
  The incorrect value "Mon" is supplied in the PIVOT operator.

  Line 36 is FOR DayNumber IN ([Mon],[Tues],[Wed],[Thurs],[Fri],[Sat],[Sun])

What am I not understanding about PIVOT tables?


texts: I turned on the configuration parameter log_planner_stats and would like to examine the details in log. But the log file is empty. It seems all the log files are changed at 12:00am of a day. Does PostgreSQL only writes log at that time? Is there any way to see the real-time log? BTW, I checked the log files in the directory pg_log which I guess is the right directory to find logs.


texts: Read massive flat file into relational database

texts:
I have a massive flat text file with ~4M records that I would like to read into MySQL using Ruby/Rails, Python, or any other method.

The flat file is roughly this format:
business_name,address,city,state,zip,employee_name

If there are multiple employees at the business, there will be one row for each employee with the business name, address, etc. repeated.

I'd like to read this in to two tables structured with the employees table having a foreign key like so:

businesses (id int, name varchar, address varchar, city varchar, etc.)
employees (id int, name varchar, business_id int)

How do I go about efficiently reading this file in so that the employees/businesses are created and referenced as needed?

Note: it is NOT safe to assume that the file is sorted.

I've tried reading the file into SQL first and then using SQL to get a unique list of business_name/address/city/state combinations that I could then insert into the businesses table, but my server runs out of memory. And this is only with 1/4th of the records loaded.


texts: MongoDB select and update with complicated conditions

texts:
A server is running multiple processes, process reads task from DB. Tasks contain status and _userid, which is external key for users collection. Look like like:

{
  _userid: ObjectId(),
  status: 'waiting',
  date: Date()
}

Status can be: 'waiting', 'in progress', 'finished'. For every user I need tasks to run ordered by date and only one at a time. How can I build a query that selects one task:

If nothing 'in progress', select first with status 'waiting' and update status to 'in progress'
If something is 'in progress', select task that has status 'waiting' and does not belong to _userid of any task that has status 'in progress' (reminder: only one at a time for every user). And also update status to 'in progress'

Is the query like this possible in MongoDB?

EDIT:
For example, I've got a tasks in DB:

{
  _userid: ObjectId(1),
  status: 'waiting',
  date: Date(1)
},
{
  _userid: ObjectId(1),
  status: 'waiting',
  date: Date(2)
},
{
  _userid: ObjectId(2),
  status: 'waiting',
  date: Date(3)
},
{
  _userid: ObjectId(2),
  status: 'waiting',
  date: Date(4)
}

In this case, first task should be chosen from tasks, because everything is 'waiting'. After first task has been chosen it becomes 'in progress':

{
  _userid: ObjectId(1),
  status: 'in progress',
  date: Date(1)
},
{
  _userid: ObjectId(1),
  status: 'waiting',
  date: Date(2)
},
{
  _userid: ObjectId(2),
  status: 'waiting',
  date: Date(3)
},
{
  _userid: ObjectId(2),
  status: 'waiting',
  date: Date(4)
}

Now, next task should be chosen with status 'waiting' and not with _userid: ObjectId(1). It will be ObjectId(2) with Date(3). After that we have 2 _userid 'in progress': ObjectId(1) and ObjectId(2), so next request should receive nothing.

{
  _userid: ObjectId(1),
  status: 'in progress',
  date: Date(1)
},
{
  _userid: ObjectId(1),
  status: 'waiting',
  date: Date(2)
},
{
  _userid: ObjectId(2),
  status: 'in progress',
  date: Date(3)
},
{
  _userid: ObjectId(2),
  status: 'waiting',
  date: Date(4)
}

When first task is finished it's status will be 'finished' and now query must receive second task for ObjectId(1), because it's status 'waiting' and nothing else runs for this ObjectId(1).

{
  _userid: ObjectId(1),
  status: 'finished',
  date: Date(1)
},
{
  _userid: ObjectId(1),
  status: 'in progress',
  date: Date(2)
},
{
  _userid: ObjectId(2),
  status: 'in progress',
  date: Date(3)
},
{
  _userid: ObjectId(2),
  status: 'waiting',
  date: Date(4)
}

EDIT 2: OK, irrelevant any more


texts: ODBC Connections locking tables in SQL Server

texts:
We have an ERP system that is a little less than modern, the backend is currently sitting on a SQL Server 2008 R2 installation. We have through mandate of management (isn't that always the case) the completely impractical practice of allowing many users to extract data from this system at will for the purposes of manipulating the data into their preferred flavor of reporting (BI, Powerquery, Access, Excel, etc)

Though all of these report generating users only have select rights, there are several other applications that have the ability to actually manipulate data for the purposes of doing things like EDI, automated data entry.

Logically this creates a number of race conditions on the tables. First come first serve, or worse still, still serving, please wait, or just NO... I believe it is further causing what has been years of previously unexplained data corruption requiring vendor assistance to correct. Because not all of the applications are properly coded to anticipate the chance that something else may have interrupted its own private intent.

So the task is to prove/correct/mitigate it where possible on a system I only recently inherited.

My first instinct tells me it cannot be done, without some sort of IPC/error/transaction control between different vendor's products and their will to fix it, control over source code, or ability to restrict it, that this CANNOT be fixed from a DBA perspective, it is simply bad practices leading to what should be anticipated as potentially unwanted side effects.

So all I can do at this point is prove it, and see that if with proof, I can solicit some sort of behavioral change in the hearts of the management that insists it has to be this way.

First step, is that I am running a continuous profiler output to disk, albeit resource intensive, I have to be able to go back and look at what has been submitted, by whom, and what impact it had. I am also using perfmon logging and PAL to try and cross reference intensive behavior on the server, "when the processor and memory are pegged, what was the SQL server doing at that instance (most likely servicing one of the GUI written, poorly performing, ten table joining, non indexed queries, that some report writer/tool tossed together"

One of those situations just occurred, we had a user in the ERP system that could not perform a function, we identified the user logged into the database that was not using the ERP software (Was using ODBC, SQL user with select only access, and MS Access), had them disconnect and the function went through. Management refuses to believe they are related because they "Should have been dealing with different tables, and the user only had select access" and I have no profile history of the instant just before it happened.

So all of that boils down to a question to the DBA fellows out there, given the task of PROVING that application A is being adversely affected by application B, what suggestions would you have to offer?


texts: SQL Server AlwaysOn AG Secondary Replica is not joined

texts:
I have created an Availablity Group on SQL Server 2019. I selected 2 SQL server for  Replicas but the Secondary replica has an error:

  This secondary database is not joined to the availability group. The configuration of this secondary database is incomplete. For information about how to join a secondary database to an availability group, see SQL Server Books Online.

When I want to add manually I give an error:

  An availability replica already exists...

What the problem?


texts:
I am trying to get my percentages to equal 100%. As of right now they far surpass 100%.

I need to account for the percentages in two different tables, ua_money_allocation and ua_expenses_by_school.

I am not sure how I can add these two tables in properly without getting:

error code 1241, "Operand should contain 1 column(s)"

Query:

SELECT 'Salaries and Wages' as 'Category',
CONCAT('$',ROUND(SUM(Value_USD),0)) AS 'Amount',
CONCAT('$',ROUND(SUM(Value_USD)/(COUNT(DISTINCT ua_money_allocation.school_ID_FK)))) AS 'Mean',
(ROUND(SUM(ua_money_allocation.Value_USD)/
    (SUM(ua_money_allocation.Value_USD)*100,1 +
    SUM(ua_expenses_by_school.Amount_USD)))) AS 'Percent of Total',
CONCAT('$',ROUND(Min(Value_USD),0)) AS 'Minimum',
CONCAT('$',ROUND(MAX(Value_USD),0)) AS 'Maximum'
FROM ua_money_allocation
Join ua_expenses_by_school
ON ua_money_allocation.school_ID_FK = ua_expenses_by_school.school_id_fk
WHERE ua_money_allocation.Report_yr='2015'
AND Value_USD !=0
AND Amount_USD!=0
AND Spending_Activity='Total Spent'

UNION ALL

SELECT Category,
CONCAT('$',ROUND(SUM(Amount_USD),0)) AS 'Amount',
CONCAT('$',ROUND(SUM(Amount_USD)/(COUNT(DISTINCT ua_expenses_by_school.school_ID_FK)))) AS 'Mean',
(ROUND(SUM(ua_expenses_by_school.Amount_USD)/
    (SUM(ua_expenses_by_school.Amount_USD)*100,1 +
       SUM(ua_money_allocation.Value_USD)))) AS 'Percent of Total',
CONCAT('$',ROUND(Min(Amount_USD),0)) AS 'Minimum',
CONCAT('$',ROUND(MAX(Amount_USD),0)) AS 'Maximum'
FROM ua_expenses_by_school
JOIN ua_money_allocation
ON ua_expenses_by_school.school_id_fk = ua_money_allocation.school_ID_FK
WHERE ua_expenses_by_school.Report_yr='2015'
AND Amount_USD !=0
AND Value_USD !=0
AND Spending_Activity='Total Spent'
group by category