texts: Is there anything wrong with linking two identical primary keys in two different tables?

texts:
As you can see here, in OrderInfo and OrderNumber, I have a relationship set. But I'm not sure what kind. I was hoping to set a relationship so if I deleted an OrderNumber, it would cascade delete the corresponding record in OrderInfo, but it won't let me. It gives me this nasty '

  can't cascade delete on identity column

error, so after removing cascade delete, it let me save it appropriately. I'm not sure what key to key means, but I know key to infinity is one to many?


texts: PostgreSQL Drop Database With Connected Users

texts:
I've searched and tried a lot, but I can't figure out a proper solution to dropping all connections so I can properly drop a database.

Force drop db while others may be connected

This topic from 2012 discusses that we should first stop allowing connections to the database. If I execute the command in step 1 I can no longer run commands.

psql -h localhost -d postgres -U postgres -c "UPDATE pg_database SET datallowconn = 'false' where datname = 'postgres';"
psql -h localhost -d postgres -U postgres -c "SELECT pg_terminate_backend (pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'postgres';"
dropdb -h localhost -U postgres postgres

The result of all commands after the first is:

psql: FATAL:  database "postgres" is not currently accepting connections

Once I'm at this point I have no idea how to recover other than stopping postgresql, deleting the database folder and recreating the database using initdb.

PostgreSQL DROP DATABASE

This tutorial doesn't include anything on disallowing connections. So I run the following:

psql -h localhost -d postgres -U postgres -c "SELECT pg_terminate_backend (pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'postgres';"
FATAL:  terminating connection due to administrator command
FATAL:  terminating connection due to administrator command
connection to server was lost
dropdb -h localhost -U postgres postgres
dropdb: database removal failed: ERROR:  database "postgres" is being accessed by other users
DETAIL:  There are 6 other sessions using the database.

I've also tried revoking access like so, but this doesn't stop PGAdmin III from connecting so I assume I messed up the command.

psql -h localhost -d postgres -U postgres -c "REVOKE CONNECT ON DATABASE postgres FROM public;"

I'm not sure how to continue. I see a lot of topics on this issue, but nothing works. Note, I'm running these commands in a shell script, but if I run them from the CLI I get the same results.


texts:
This is the connectivity info:

And this is the configuration:

Public connection is allowed, vpc acl inbound:

What am I missing?
Edit: when I try to connect I get an error as if the endpoint is inaccessible. I suspect I'm missing a pem file or ca?


texts:
Please help me in understanding the below issue :

SQL Server Version 2014 with AOAGs. We have separate Data , Log , temp DB , SQLFS Drives individually.

Our Backup Strategy is as below:

Weekly Full Backup
Daily Differential
Every 15 Minutes Log backup

My Total Log drive space is 300 GB.
When I checked the DBCC SQLPERF(LOGSPACE) only tempdb is highest consumer.

AutoGrowth Settings :

NOTE : Currently I have only 20% free space left. Autogrowth is in '%'.

Also we have Filestream data enabled . Will Filestream have separate Log file ?

Result of T-Log utilization:

Please help me with below concerns as I am new to SQL Server DBA.

In what case I should go for increasing my Log drive space . For ex: E:\ is for my SQLLOG, in what case I should expand or increase my drive. What is the end point I should consider before expanding drive ?
Sometimes DBCC Shrink does not free up much space ! what could be the valid reason.
And I read some articles where it is mentioned not to use Autogrowth in '%' instead we should use in 'MB's'. I have mentioned my case above . Kindly correct me if I am in good lines or false affect. Please correct me and help me on going for good considerations.

Thanks


texts: Error Updating SSIS package to 2014 due to script component

texts:
I am trying to update a SSIS package that contains script component. The script component just gets the error description as described here.
I am using the update wizard in SSDT 2015 which should be compatible with SQL Server 2014. (ssdt)
After the Package update wizard runs, I get a warning and information for every data flow that has a script component.

Warning 0x40016044: Load table TableName: Found SQL Server
Integration Services 2012 Script Component TableName Error
Desc that requires migration!
Information 0x4001601a: Load table TableName: The
TableName Error Desc has been migrated. The package must be
saved to retain migration changes

This looks to me like the script component has been migrated successfully. I then build and deploy the package to my SSIS instance.
When I try to execute the package on the instance, via SQL agent or Integration Services Catalog, the package fails verification with the following errors.

Load table PersonDisability:Error:
Microsoft.SqlServer.Dts.Pipeline.ComponentVersionMismatchException:
The version of tablename Error Desc is not compatible with this
version of the DataFlow.  [[The version or pipeline version or both
for the specified component is higher than the current version.  This
package was probably created on a new version of DTS or the component
than is installed on the current PC.]]    at
Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostCheckAndPerformUpgrade(IDTSManagedComponentWrapper100
wrapper, Int32 lPipelineVersion)
Load table TableName:Error: The component metadata for
"TableName Error Desc" could not be upgraded to the newer
version of the component. The PerformUpgrade method failed.

Based on these, if the messages are accurate, I have a version mismatch. My instance is 2014 SP1 CU3 (12.0.4427.24) and my SSDT that I did the package update with is 2015(14.0.51128.0)
Are these versions incompatible?


texts: OS error 13 when running mariabackup as non-mysql user

texts:
All:

I'm trying to set up regularly-scheduled backups of our MariaDB cluster with mariabackup, and have set up an account for this purpose.  The account is a member of group mysql. But when I run the backup as that user:

$ mariabackup --backup -u [myid] -p [password]
mariabackup: Generating a list of tablespaces
2017-12-08 17:49:07 140352620800128 [ERROR] InnoDB: Operating system error number 13 in a file operation.
2017-12-08 17:49:07 140352620800128 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.
mariabackup: error: xb_load_tablespaces() failed witherror code 11

If I run with sudo -u mysql, the exact same command works fine.

Is there a way to turn on extra debugging or verbosity so I can see WHICH file or directory it's having trouble accessing?

MariaDB v10.2.11, CentOS 7 (x86_64)


texts: mysql innodb index: better one big or many compact?

texts:
I've a question for an expert in how innodb indexes are structured (mysql 8.0.18)

Say I have 4 varchar columns on a table with one billion rows:

country, state, city, attraction

I have queries that look for all or certain attractions, either by country, state, city or attraction name.

query1: "select * from table where attraction like 'asd%' and country = 'X'"
query2: "select * from table where attraction like 'asd%' and country = 'X' and state = 'Y' and city = 'Z'"
query3: "select distinct attraction from table where country='X'"
query4: "select distinct attraction from table where attraction like 'Ux%'

Combined index: (attraction, country, state, city)

This index would cover all 4 queries.

Can I expect similar performance on query1,3,4 in comparison to a specialized index ?

Specialized index1:  (attraction, country)
Specialized index2:  (attraction)

I don't have the time to dive into the details of innodb storage, I hope someone did that already ;)

My main thoughts on this:

More indexes will need more memory and storage (given a billion rows quite a bit), so that's a concern.
If an index that's made for 4 columns is called on a query that only needs one (the first) column or on two (the first two) columns is the data access sequential and as effective as when having small dedicated indexes (that basically contain duplicate data)?

So should I have one index, covering the WHERE requirements of all 4 queries or 3 indexes, each dedicated to the query it serves ?


texts:
I am looking for a free solution to set up Mysql in an active/passive configuration, in order to provide high-availability. In particular, a Bugzilla installation, and MediaWiki needs to stay alive (which produce a dependency for MyISAM support).

I have two sites, where one site is the primary site, and one site is the backup site, with minimal latency between the two (<1ms). My plan is to set up replication between two Mysql installations, and use Mysql proxy or HAproxy at the webserver node as the failover mechanism. The goal is to allow for the primary site to go down, without affecting the backup site (users are located at both sites).

For replication, I was looking at semisyncronous replication - mainly since it appears to provide limited data loss on outage, and have a solution for network congestion.

My concerns are as follows:

Would the above constitute a robust solution?
What would happen if primary site is in a flapping state?
What would happen on network outage between the sites (split brain)?
Coping with data corruption (faulty hw)?

How can I set up my servers to provide full consistent database service for the web applications?

*Edit: Due to a dependency on MyISAM, I've found that the following solutions won't work for me:

Galera/Percona
DRDB


texts: MySQL - How to convert an existing int column with duplicates to auto-increment?

texts:
After adding some thousand records in a table, I noticed that the "Primary" and "Auto-Increment" properties of the main column are removed. Now instead of auto-incremented numbers, most of them (but not all) are just "0".

Any idea how to fix the issue? All "0" values should be updated to: Max(int) of column + 1.


texts:
When testing stored procedures in SSMS, it sometimes says there is a missing index on
#someTempTable____________________000000000000005B] (someField) etc etc

When I do add them like this to the sp:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[#someTable] ([someField])
GO

I never seem to see a speed improvement.  So, I often do not add such indexes.  When should I be adding such indexes?


texts: Column cannot be converted from type 'int' to type 'bigint(20) unsigned'?

texts:
I have a Percona MySQL cluster, with a master and a few slaves. Each of them is running "Ver 14.14 Distrib 5.5.40-36.1" of MySQL. Replication is row based.

I ran an alter query on one of the tables of the slave only. The plan is to run this query on all slaves and then do a master switch as we cannot afford to lock tables in master.

The query is:

ALTER TABLE order_item_units
MODIFY parent_id BIGINT(20) unsigned ;

And post this when I checked the slave using show slave status, I see the replication is broken with following error:

  Column 3 of table 'database_name.order_item_units' cannot be converted from type 'int' to type 'bigint(20) unsigned'

And when I checked the column, it did convert into BIGINT.

And now I am not able to fix this. I did stop slave and start slave, didn't help. Did stop slave and MySQL restart, didn't help. Did a skip counter, didn't help either.

Either the column should not have gotten converted, then the error would have be just, but then may be there should not have been any error at all.

And if the column did get converted, then why the error?

Any clue what am I missing here?


texts: What is the Wait Type "QDS_Shutdown_Queue" referring to?

texts:
I'm gathering wait types on a load test environment, and the second biggest wait type is QDS_SHUTDOWN_QUEUE. The stats are coming from sys.dm_os_wait_stats, but I can't find any documentation that explains what that particular type means.

I'm running SQL Server 2014, SP1 on a physical Windows 2008 R2 instance (no VM involved).


texts:
I am writing a drop statement (in phpMyAdmin) to get rid of some unneeded tables in my database. If I run this query against our live database I am not seeing the full drop statement (it is getting truncated after 50 characters). If I run it against a copy of that database I get the full statement returned.

Any ideas why the behaviour would be different?

SET GROUP_CONCAT_MAX_LEN=10000;
SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name) , ';' )
AS statement FROM information_schema.tables
WHERE table_schema = 'dbname' AND table_name LIKE 'prefix%';


texts: Can't open Microsoft Access Database inside a Microsoft SQL Server Filetable Folder

texts:
We recently started to migrate the Data on our Fileserver to SQL Server (Version 14) Filetables and I encountered a problem with MS Access Databases (.accdb, Access 2007).

When the .accdb file is located in a "normal" Windows shared folder on the server, I can open it with no problems.
When I copy the same file into the Filetable folder, i get an exception that the Database is locked by an unknow user and can't be opened or locked.

All other filetypes I tested work fine on the Filetable share.

The users log into the Fileshare with local accounts on the server, I've added permissions for this accounts to SQL Server.
Is there something different to take account for with MS Access databases that are located in Filetables regarding permissions?


texts: Change Data Capture not running when Mirror is Suspended

texts:
We have 2 SQL 2012 SP2 Enterprise servers that have some mirrored databases. One of these databases also has CDC enabled on a number of tables so that we can run some business logic when there are changes to the table.

Yesterday we did some maintenance on the mirror server (the primary was running exposed). A few hours later we re-enabled the mirror and it synchronised as expected.

What we found out was that the CDC data that we normally process close to real time didn't get collected until after the mirror was resumed.

2 questions out of this:

When you have a mirror configured, is CDC data only collected when the mirror is synchronized?
Is there anyway to make CDC capture run when a mirror is paused?


texts: Oracle SQL: How to give user the permissions to SELECT from a table?

texts:
I'm not very familiar with Oracle SQL at all, but using our admin account I ran the following query on our Oracle database server: GRANT SELECT ON Table1 TO OtherUser. I then committed this change (not sure if you need to even commit this?)

I'm using Oracle SQL Developer to connect to our Oracle database and after I ran the above GRANT statement from our admin account, I would've thought I would see Table1 listed under the Table objects node when I connect as OtherUser, but I do not see it and cannot select from it when I try.

Is there any other permissions I need to grant, or anything else I need to do that I missed?

Edit: When I query DBA_TAB_PRIVS I do see Table1 listed with the SELECT privilege for grantee OtherUser, even though from OtherUser's perspective I can't see Table1 under the Tables node in Oracle SQL Developer.


texts: Does the elapsed_time column in the v$sql table indicate the time per query exeuction or is it a sum of all executions?


texts:
I have a role named db_executor and need to find out what permissions it has on what objects. I found List all permissions for a given role? but the only solution that remotely works is:

SELECT DB_NAME() AS 'DBName'
      ,p.[name] AS 'PrincipalName'
      ,p.[type_desc] AS 'PrincipalType'
      ,p2.[name] AS 'GrantedBy'
      ,dbp.[permission_name]
      ,dbp.[state_desc]
      ,so.[Name] AS 'ObjectName'
      ,so.[type_desc] AS 'ObjectType'
  FROM [sys].[database_permissions] dbp LEFT JOIN [sys].[objects] so
    ON dbp.[major_id] = so.[object_id] LEFT JOIN [sys].[database_principals] p
    ON dbp.[grantee_principal_id] = p.[principal_id] LEFT JOIN [sys].[database_principals] p2
    ON dbp.[grantor_principal_id] = p2.[principal_id]

WHERE p.[name] = 'db_executor'

The problem is that the ObjectName and ObjectType is NULL. So while I know it only has the EXECUTE permission in a state of GRANT I don't know to what object(s) that applies to. Is there a better way to get this list or how can I modify this code to list the Object(s)?


texts: Which programming languages will have problems pulling dash named column names?

texts:
I sometimes work in clojure, a language promoting the use of dashes in variable names (much more readable than Java camelCasing I can say). Taking this a step further, I'd favor having column name parity in one database implementation, so that column names in my code are the same as in the database. My database of choice supports this. I wonder how this would affect developers using other languages, who may have to access the same tables:

Will developers in other common languages have to go through special effort to accommodate reading dash inclusive column names? e.g. I once heard rants from PHP developers around hyphens in names of resources. Which programming languages will require extra handling for dash inclusive column names? what extent of effort will those languages require for it?


texts:
I'm working on getting full grasp of logging architecture.
Please correct me if I'm wrong here:
I understand that a checkpoint occurs (also lazywriter) to write the dirty pages in the memory to the disk. Disk here means transaction log file no matter what recovery model the database is in. Then the dirty pages become clean, i.e. data on the disk is up-to-date with memory pages.
I also understand logging must happen after commit and before sending the acknowledgement to the client.
What cloudy is, what happens after the data is written to disk on the transaction log file? What moves the data from ldf to mdf (or ndf)? In case a lazywriter removes the pages off the memory, the data will be in the transaction log but not in the data file (yet) or no longer in the memory. What happens when that page is queried?
Also a brief explanation into relationship between checkpoints and simple-full recovery models is appreciated. How does the behaviour change?
Thanks for helping out.


texts:
I'm using PostgreSQL 9.6 on Leap 42.3. I've compiled and installed pg-strom. Everything seems to work fine:

LOG:  PG-Strom version 2.0devel built for PostgreSQL 9.6
LOG:  PG-Strom: GPU0 GeForce GTX TITAN X (3072 CUDA cores), 1215MHz, L2 3072kB, RAM 12207.19GB (384bits, 3.34GHz), capability 5.2
LOG:  PG-Strom: GPU1 GeForce GTX TITAN X (3072 CUDA cores), 1215MHz, L2 3072kB, RAM 12207.19GB (384bits, 3.34GHz), capability 5.2
LOG:  NVRTC - CUDA Runtime Compilation vertion 7.5

Until I try to create the extension:
postgres=# CREATE EXTENSION pg_strom;
ERROR:  could not find function "plcuda_function_validator" in file "/usr/lib/postgresql96/lib64/pg_strom.so"
Compilation of pg-strom ended without error. Also starting postgres with pg-strom extensions works without error.

plcuda_function_validator does not seem to be in the lib but another plcuda function is:
    nm /usr/lib/postgresql96/lib64/pg_strom.so | grep plcuda_function_validator
    nm /usr/lib/postgresql96/lib64/pg_strom.so | grep plcuda
00000000002c4010 D pgstrom_cuda_plcuda_code

Also compilation of the objects gpujoin.o, gpupreagg.o, gpusort.o, and pl_cuda.o is commented out in the Makefile which I cloned from the pg-strom git repo. When I try to compile these objects, compilation fails. I guess this part of pg-strom is not yet finished. Is pg-strom usable at all currently with postgres 9.6?

Thanks for any hints!

Robin

Edit: Solved by upgrading to Cuda 8.0 and installing Master branch instead of pgsql9.6 branch of pg-strom.


texts: How can I print the contents of a table to the console?

texts:
My customer sends me complicated (and inconsistent) data. I am using C# and calling stored procedures in the database using the Entity Framework 6 to insert that data into the database. (The Entity Framework portion is only really handling data retrieval and ensuring a correct schema; inserts are done completely through stored procedures using table-valued parameter procedures.)

I use a series of Select DISTINCT statements to pull the unique entities out of the data and into temporary tables, then use those temporary tables to update the live database after verifying the data. (Credit to @srutzky for the idea.)

I want to be able to print the contents of those temporary tables during the procedure so I can inspect it and/or write it to a log. The data they send is messy, and I want to make sure that my SELECT DISTINCT queries are working right. (Spoilers: they are, but the customer's data has more holes in it than decaying Swiss cheese.)

The data can be inconsistent as well, so sometimes the same entity will have different types values on a field that it shouldn't, which will cause the DISTINCT query to select the same entity multiple times from the data and violate UNIQUE constraints in the table. I'm specifically trying to locate these "bad lines" of data both for logging purposes and to inform the customer that the data was wrong.

This all has to be automated in C#. The program, when finished, will run on a remote server that I will not have write access to, so I also need the entire process to be "hands-off" (no sqlcmd, no SSDT, etc. Just T-SQL.)

What is the best way to do this?


texts: Help on deciding how to pick Correct Index

texts:
I would like to ask for some help to create correct indexes for this query:

SELECT DISTINCT D.TypeID
FROM dbo.Sizes AS S
WHERE EXISTS (
        SELECT 1
        FROM #PermissionsTable AS PT
        WHERE PT.ProductID = S.ProductID
            AND PT.CountryID = S.CountryID
        );

This query is used in procedure and #PermissionsTable is created in there based on passed in criteria.

I've tried creating various indexes on my table, but I'm always getting only Index Scans, I'd like to get Seeks of course. For instance:

CREATE NONCLUSTERED INDEX idx_Sizes_ProductID_CountryID_TypeID
    ON dbo.Sizes (ProductID, CountryID, TypeID);

CREATE NONCLUSTERED INDEX idx_Sizes_CountryID_ProductID_TypeID
    ON dbo.Sizes (CountryID, ProductID, TypeID);

-- I've added TypeID into INCLUDE part, because I'm not using it in any clause except SELECT statement.
CREATE NONCLUSTERED INDEX idx_Sizes_ProductID_CountryID
    ON dbo.Sizes (ProductID, CountryID) INCLUDE(TypeID);

CREATE NONCLUSTERED INDEX idx_Sizes_CountryID_ProductID
    ON dbo.Sizes (CountryID, ProductID) INCLUDE(TypeID);

And on #PermissionTable I've tried creating both Clustered and Non Clustered indexes on ProductID, CountryID or CountryID, ProductID.

But I'm always ending up with Scans.

Sizes table has hundreds of millions of rows. Permissions table has around 400.000.

At the moment I've added WITH (FORCESEEK) hint next to FROM dbo.Sizes AS S, which forces seek, but I'd like this to be done by SQL Server engine.

Any literatur, tips, anything would be helpful.

Thanks!

Update: added execution plan


texts: SPN for Availability group(AG) listener for SQL Server 2016

texts:
Requesting some inputs..
We have a 2 node SQL 2016 Availability group configured and apps currently use primary replica for connection. We are now planning to use listener instead. Questions is, how to make sure we are using kerberos authentication for application connectiions?. Is it mandatory to manually register SPN for the listener? or is it automatically created?. is this a one time activity?. How to make sure SPN is registered automatically for the listener when the AG restarts or failover to second node?. Appreciate all the responses.


texts: How to save response file in DBCA oracle 12c

texts:
I have read the oracle documentation for 12c on saving response files from dbca and have found this:

  Starting with Oracle Database 11g Release 2 (11.2), you can save all
  the installation steps into a response file during installation. You
  can click the Save Response File button on the Summary page to do
  this. Later, this file can be used for a silent installation.

However, I am at the summary page in DBCA and do not see an option to record a response file. I poked around other pages in DBCA and could not find it either. Does anyone have any information on how to record a reponse file for 12c DBCA?


texts: Rebalance/move or strink oracle undotablespace

texts:
I'm running out of space in oradata01 and oradata02. Is there a ways to move or rebalance with oradata03?

/opt/oracle/oradata01/x/undotbs01.dbf   4   100 %
/opt/oracle/oradata02/x/undotbs02.dbf   17  100 %
/opt/oracle/oradata03/x/undotbs03.dbf   25  1 %


texts: Does SQL Server use (unused)index stats for optimizer

texts:
I am wondering about unused index in MS SQL Server. By the Index usage DMV I can identify an index which has not been used for seeks, scans or lookups.

However I know from Oracle that an index might not be used in such a way in a execution plan, however it can still contribute statistics/cardinality information to the (Oracle) optimizer. This contribution is not monitored in the same way.

So I am wondering if in MSSQL a Index can have a similar positive effect even when it is not directly used (in a representative time frame)? And specifically, if it can be better than a column statistic (I.e. dropping the index would be harmful).

I haven’t seen this mentioned in any of the index tuning articles I have come along, so I assume MSSQL (up to 2017) does not have this concept, is that correct?


texts: index of aggregate value with fast update?

texts:
Is it possible for me to index the sum of a particular table column, such that future lookups will not have to scan the entire table? This table will grow to be very large and a sum of the entire table at once could be quite costly.

And just as importantly, can I trigger future INSERT|UPDATE|DELETEs to update the sum without re-adding every term? (For example, an INSERT should add the relevant column's value from the new row to the existing sum.)

Will materialized views help with this goal? This SO question seems to suggest that they will, but I'm not sure how.


texts: Query to get counts of values per column

texts:
I have a big table of vendor-supplied data (that I can't change around much) with about 315 columns. I suspect that many of the columns are not being used (or at least not consistently).

I'd like a query that can give me the count per column of the values in the table.

For example

CREATE TABLE foo AS VALUES
    ( null   , 'xyz'  , 'pdq'  , null ),
    ( 'abc'  , 'def'  , 'ghj'  , null ),
    ( 'hsh'  , 'fff'  , 'oko'  , null );

So this would give results something like:

Col1 | 2
Col2 | 3
Col3 | 3
Col4 | 0

EDIT: to clarify, I know I can just use COUNT but I'm hoping for a way to loop over possibly a query to the system table first to avoid having to hand code 315 count statements. Thanks!

Something like

FOR column_names IN SELECT * FROM information_schema.columns WHERE
table_schema = 'public' AND table_name = 'vendor'
LOOP
 RAISE NOTICE 'doing %s', quote_ident(column_names.column_name);
 SELECT count(column_names.column_name) from vendor
END LOOP;


texts: Why does this MySQL query take 25+ seconds?

texts:
The query is:

SELECT T.ID, T.A, T.T
FROM  table1 AS T
WHERE (userID='333335') AND (category LIKE '1%') AND (T.T='You Ain\'t Much
Fun' AND T.A='KEITH, TOBY') GROUP BY T.A, T.T

I have a BTREE index over 2 columns - userID, and another column which doesn't feature in the above query. The index is set up with userID being the first column.

The table is innoDB, and the total table size is around 70 million, but the rows that userID='333335' equals total only about 500,000.

If userID has an index on it, and that's the first column queried, shouldn't the rest of the query be able to breeze through 500,000 rows pretty quickly?

What is it that I'm overlooking please?

Thank you for your time and help.


texts: How can I temporarily stop log shipping --> Do the task --> Start the Log shipping ? Is it possible ?


texts: Setting MySQL slow query to less than 1 second

texts:
I have a heavily loaded LAMP system serving about 40 requests per second and doing about 300 MySQL queries per second. Here is the output of MySQLAdmin status:

Uptime: 5051  Threads: 1  Questions: 1418500  Slow queries: 0  Opens: 456
Flush tables: 1  Open tables: 450  Queries per second avg: 280.835

However, the global status show about 5-10% table lock contention.

So, I want to figure out the queries that are taking longer time.

Is it possible to set the slow query log to less than one second? For my queries, even execution time of 100ms is high.


texts:
I have numerous tables with some funky character replacement going on. I found some information on running an update to replace these, but it's in numerous tables and numerous fields.

update <table_name>
set <field> = replace(<field>,'â€™','\'');
update <table_name>
set <field>= replace(<field>,'â€¦','...');
update <table_name>
set <field>= replace(<field>,'â€“','-');
update <table_name>
set <field>= replace(<field>,'â€œ','"');
update <table_name>
set <field>= replace(<field>,'â€','"');
update <table_name>
set <field>= replace(<field>,'â€˜','\'');
update <table_name>
set <field>= replace(<field>,'â€¢','-');
update <table_name>
set <field>= replace(<field>,'â€¡','c');

Do you all have any advice on running this across every field for all the tables? I thought about doing a mysql dump and using sed to replace everything, but I'm not sure how to structure the commands since they're all special characters.

Any help would be greatly appreciated.


texts: Best Format to Bulk INSERT/UPDATE Data to SQL SERVER using C#

texts: Can anyone tell me what is best way to bulk insert/update data to SQL SERVER 2012 using C#? I have an excel file which I am passing as TVP(table value parameter) to a SP. But this is little bit slower. Any hint will appreciated.


texts: Unable to drop non-PK index because it is referenced in a foreign key constraint

texts:
I have a table named MyTable. The primary key is an identity int column named MyTableID. There is a unique clustered index on the PK column MyTableID named PK_MyTable.

I noticed there is an additional non-clustered unique index IX_MyTable_MytableID on that table with a single column MyTableID, and no other included columns. This index is obviously redundant, but when I try to delete it, I get an error message:

The constraint 'IX_MyTable_MyTableID' is being referenced by table 'OtherTable',
foreign key constraint 'FK__OtherTable__MyTableID__369C23FC'.

Why is the FK constraint relying on the non-clustered unique index instead of the primary key constraint? How do I update the FKs to use the clustered PK index instead of the other index?


texts: Fast Backup and Restore of Growing Databases

texts:
I work in Tech Support and the some of the products I support are heavily database dependent (PostgreSQL and MySQL, but most of the big ones are MySQL). Before we do an upgrade, we always take a full backup of the database, in case we need to roll back. Upgrades often perform modifications to the schema and there is no mechanism to reverse them if problems are discovered after upgrade.
The problem is that most of our customers have short windows to take the backup, do the upgrade, test the heck out of it, and potentially roll back. There are some long-term customers where the amount of time required to take a backup is now taking up a significant portion of the upgrade window.
Much of the data is no longer updated. A lot of it is not even used but, because reasons (let's just assume they're all justified), it is not purged from the database.
Is there a method where a large portion of the database could be set as read-only and treated separately? It would still need to have schema massage done when required but could be done to a copy outside of the upgrade window.
From what I've read of partitioning, it seems like a candidate. If I understand correctly, large tables can be split and remain the same table. Or does it make more sense to move the old data to different "archive" tables that are unchanging, except for when more data is added.
Would differential backups also do what I want?
I'd like a more fully formed pitch than what I have now before I suggest it to R&D. I'm fine with doing more research, but lack the practical experience to know what avenues to investigate.
Thanks.
2020-11-21 UPDATE: LVM and Storage Spaces
With some research complete, Storage Spaces is Microsoft's current (fairly well reviewed) implementation of what Linux has been good at for a while, LVM. We have both Windows and Linux servers in use.
The nice thing about that approach is that it shouldn't require any modification to or accommodation by our applications; the magic is done at a level below their awareness. Of course, everything would still need to be tested, but I haven't encountered anything discouraging.


texts: Restored Database is different from the original

texts:
I have recently performed a restore of our data-warehouse staging database onto a separate instance in our dev environment.   After the restore I did a comparison and it looks like the database sizes are different.  Staging_Original is 8246 MB while Staging_New is 7571.63 MB.  It appears that 2 tables in the new db are missing and for some reason the new database has a table that doesn't exist in the old database.  How is this possible if I've performed a full backup and restore?  How can I get an exact copy of one database and restore it into a second instance?  Both of the model databases appear to be the same as well.

Backup scrtip:

BACKUP DATABASE [BI_Staging] TO  DISK = N'G:\BI_Staging.bak'
  WITH NOFORMAT, NOINIT,  NAME = N'BI_Staging-Full Database Backup',
  SKIP, NOREWIND, NOUNLOAD,  STATS = 10, CHECKSUM
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset
  where database_name=N'BI_Staging'
  and backup_set_id=(select max(backup_set_id)
    from msdb..backupset where database_name=N'BI_Staging' )
if @backupSetId is null
begin
  raiserror(N'Verify failed. Backup information for database ''BI_Staging'' not found.', 16, 1)
end
RESTORE VERIFYONLY FROM  DISK = N'G:\BI_Staging.bak'
  WITH FILE = @backupSetId,  NOUNLOAD,  NOREWIND
GO

Restore Script:

USE [master]
GO
ALTER DATABASE [BI_Staging] SET  RESTRICTED_USER WITH ROLLBACK IMMEDIATE
GO

RESTORE DATABASE [BI_Staging] FROM
DISK = N'G:\BI_Staging.bak'
WITH  FILE = 1
,  MOVE N'BI_Staging' TO N'E:\MSSQL10_50.BI_DSS2\MSSQL\DATA\BI_Staging.mdf'
,  MOVE N'BI_Staging_log' TO N'F:\MSSQL10_50.BI_DSS2\MSSQL\Tlogs\BI_Staging.ldf'
,  NOUNLOAD,  REPLACE, RECOVERY, STATS = 10
GO


texts: MySQL: Does the order of the fields in a table make a difference in performance?

texts:
Specifically I am wondering if I should place all my fixed length fields like int, timestamp, char at the beginning of the CREATE TABLE and place all the variable length like varchar at the end.

Also, I am wondering if TEXT fields are treated differently.


texts: SQL Fast database restore with Azure Virtual Machine instant recovery snapshot and SQL in Azure VM?

texts:
I have a large database. If I want to restore to a point in time using a full back up, it takes around 4 hours + time taken for transaction log restores.

I also have VM image instant restore snapshots taken on Azure daily. The restore of a VM image by replacing existing disks takes only 20 minutes.

It is known that transaction logs cannot be restored unless the most recent full back up is restored.

Lets say I sequence my backup jobs in this order:
1. Full SQL Backup of the database taken and stored on Azure using "SQL in Azure VM" feature of the Recovery vault
2. Azure VM image instant restore back up following it (there may be one hour in between with transactions occurring)
3. Transaction log backups using the same feature on Azure as #1, every 15 minutes

Then if we have an outage, can I do a faster restore by restoring the VM image? Would I be able to skip the full backup restore and directly restore only the transaction logs in this state of the VM? Is this a sound theory?

The LSN of the database should be at the state when the VM snapshot was taken. Would it be a problem since the database would come online immediately and the LSN would change before I can take it offline and do a restore?


texts: Implications of using a Unique NonClustered Index with Covering Columns instead of a Primary Key

texts:
We have a large table [MyTable] which currently has both a Primary Key, and a Unique Non Clustered Index on the same column ([KeyColumn]). The U NC index also has additional covering columns.

Having both PK and Unique NC Index on the same columns seems redundant, so I was considering deleting the Primary Key and instead using the Unique Non Clustered index for Referential Integrity purposes.

Note that the table is clustered by another column entirely.

i.e. So we have:

ALTER TABLE [MyTable]
    ADD CONSTRAINT [PK_MyTable]
    PRIMARY KEY NONCLUSTERED ([KeyColumn])
GO

AND

CREATE UNIQUE NONCLUSTERED INDEX [IX_MyTable_SomeIndex]
    ON [MyTable] ([KeyColumn])
    INCLUDE ([Column1], [Column2])
GO

As far as I know, it is not possible to add covering columns to a Primary Key, so I intend to do:

Drop the Foreign Key Constraints reliant on MyTable.KeyColumn
Drop the Primary Key on MyTable.KeyColumn entirely
Re-Add the foreign keys to the table (i.e. RI will be enforced via MyTable.KeyColumn)

The only implication I can think of doing this is that we won’t get the visual key symbol on our ERD diagrams, and that the (leaf) index density will be less because of the included columns.

I've read https://stackoverflow.com/questions/487314/primary-key-or-unique-index and am happy with the integrity and performance aspects of doing this.

My Question is : Is this approach flawed?

Edit
What I'm trying to accomplish : Performance Optimisation and Spring Cleaning. By removing either the PK or an Index, there will be less pages needed for my indexes = faster writes, plus also maintenance/operational benefits, i.e. one less index to keep defragged etc.

To put some background to this, I've never before had a table which is being referenced, without a PK. However, the fact that a NC Index with covering columns was added to the table means that I need to adapt my thinking.


texts: How to know if I am running SQL Server 2008 R2?

texts:
I ran @@version.

This is what it gave me:

Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64)   Sep 16 2010 19:43:16   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.0  (Build 6002: Service Pack 2)

How do I know if this is r2?


texts:
Is there a way to save the username and the IP from which the user tries to login to a MySQL server??

Enabling general-log seems to log everything but that seems to write too much details taking more space and also there is a possibility of saving password details too hence it would be helpful if there are any other options available.


texts: Performance Hit when upgrading to Oracle 11gr2

texts:
I am experiencing the same symptoms as noted in this question which was closed and does not have a satisfactory answer.

Original Question
We've recently upgraded from Oracle 10gR2 (10.2.0.4 ) to 11gR2 (11.2.0.3) and we are noticing a significant hit in performance although execution plans are the same for the offended queries before and after the upgrade. Allocation of more memory did improve the performance but just slightly. We also tried to set optimizer mode to 10204 at session level but queries produced no better results. The only difference we've noticed is when enabling autotrace the physical reads on 10gR2 were 0 whereas on 11gR2 it was of the magnitude of tens of thousands.

Including stats demonstrating the physical reads discrepancies:

10gR2 Stats
~~~~~~~~~~

Statistics

      0  recursive calls
      0  db block gets
  21189  consistent gets
      0  physical reads
      0  redo size
   1014  bytes sent via SQL*Net to client
    492  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

11gR2
~~~~~~

Statistics

      0  recursive calls
      0  db block gets
  11243  consistent gets
  11232  physical reads
      0  redo size
   1049  bytes sent via SQL*Net to client
    524  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

Any ideas/suggestions/right-directions would be greatly appreciated.


texts: Restore deleted data from a Table which occurred daily

texts:
I am not sure whether this is restore data scenario is possible.
Scenario:

I have a table with few 100 data inserts every day.I didn't realize that because of some reason(identified later as a bug in code),the data was getting deleted the same day whenever a new record which was not related that process comes into the table.So there is no specific time that this delete happens.
We identified this after a week(yesterday),and i added a delete trigger for that table and stores the data into another table.

The issue is how can i restore past week's deleted data.
I have transaction log backups which was taken every one hour.
Without knowing the specific time the data got deleted,how can i do point in time recovery.
Any restore strategy.?


texts: Can we use old primary to sync with standby after failover in PostgreSQL?

texts:
If my standby (postgres) is behind by few seconds before failover. What is the easiest way to sync back my old primary after failover in PostgreSQL.

In Oracle we have the option of Reinstating a Failed Primary Database, if flashback is enabled.

Do we have any such option in postgres if I have all the WAL archives generated after failover in new primary server? Or do we need to go for complete rebuild of standby?


texts:
I have a problem with long execution of select query first time in the morning. I guess it is because this query is not cached and SQL Server is caching it. Other few possible causes were checked and set aside.

I would like to make an stored procedure that will execute each 8 a. m. or each few hours, for example, and cache my sql query.

I can't figure out does this method to fight with long performance in first time is fine? How else I can force to cache my query?


texts: If you don't rollback a transaction will the change still be made?

texts:
Say I am running a query

begin tran

update users
set name = 'Jimmy'
where name = 'john'

If I DON'T rollback the transaction, will these changes still be made, will it throw an error or will it act as a rollback anyway?


texts: When to cluster a table using an index? [postgresql]

texts:
https://www.postgresql.org/docs/9.1/sql-cluster.html
Now suppose I have pg_repack installed so that it won't lock the table when clustering.

Should you cluster a table using an index that has a high idx_scan?

It does not make sense to cluster a unique uuid index right? Since reordering the table won't help to retrieve information easily.

Would it make sense to do a cluster on a composite unique index?

If a particular column has been joined (inner/left) with several queries, would it make sense to cluster a table related to the column's index?

What exactly we should look for when selecting a particular index to cluster a table on?


texts: IBM Bluemix SQL Database service data store management

texts:
I'm working on IBM Bluemix SQL Database service with premium plan. According to our application the sql service can have many schemas.

Is there any way to seperate data storage of each schema to different physical storage and manage them as individual(file, file group etc.)

For example: The tables and data on SchemaUser1 stored in SQLDBFile1 and SchemaUser2's tables and data stored in SQLDBFile2 Is it possible to create schemas like this? or the only way to separate datastore is creating a new sql database service.


texts: MongoDB: How best to maintain a live attachment from a db doc to a changing file on the fs?

texts:
We have a system where users are working with (json) files in a directory, manipulating them directly, possibly moving them around, renaming them, etc. There is also another app outside our control depending on the presence and integrity of these files (so we can't just put them directly in the db).

We'd like to maintain in mongo a metadata document about each file.

Is there a way, using GridFS or something similar, to maintain a live connection between the mongo doc and the file on the file system, even as the user moves the file around?

We're considering capturing fs events to update the mongo doc, but we're worried about the reliability and robustness of such a solution.

Any other thoughts most welcome!


texts: Index dive/sampling row count very inaccurate in MySQL

texts:
The table emp_address_phone is filled with many random values.

CREATE TABLE `emp_address_phone` (
  `address_no` int(11) NOT NULL,
  `emp_no` int(11) DEFAULT NULL,
  `street1` varchar(255),
  `street2` varchar(255),
  `landmark` varchar(255),
  `city` varchar(50),
  `state` varchar(50),
  `country` varchar(50),
  `phone` varchar(20),
  PRIMARY KEY (`address_no`),
  UNIQUE KEY `emp_no` (`emp_no`),
  UNIQUE KEY `phone` (`phone`)
) ENGINE=InnoDB AUTO_INCREMENT=21001 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

The query is on address_no column.

select * from emp_address_phone where address_no > 90;

There are really 6427 rows totally and all of them satisfy the criteria also. But the estimate was 3118.

From optimizer trace:

{
    "index": "PRIMARY",
    "ranges": [
      "90 < address_no"
    ],
    "index_dives_for_eq_ranges": true,
    "rowid_ordered": true,
    "using_mrr": false,
    "index_only": false,
    "rows": 3118,
    "cost": 317.29,
    "chosen": true
}

I changed the number of pages that index dive algorithm samples to 300 from 20 in the cnf file. So server startup with innodb_stats_persistent_sample_pages value 300. I run Analyze table. But the estimate is still way off.

Trace when innodb_stats_persistent_sample_pages = 300

{
    "index": "PRIMARY",
    "ranges": [
      "90 < address_no"
    ],
    "index_dives_for_eq_ranges": true,
    "rowid_ordered": true,
    "using_mrr": false,
    "index_only": false,
    "rows": 3213,
    "cost": 322.81,
    "chosen": true
}

The total number of pages in clustered pk index was 289 pages. So all the 289 must have been used for estimation right? Why is it still way off?

Is the innodb_stats_persistent_sample_pages variable's purpose different? If so, is the number of pages that is read while index dive not controllable?

Also, innodb_buffer_pool_load_at_startup and innodb_buffer_pool_dump_at_shutdown are off.


texts: Oracle hierarchical query: aggregate over each node's descendants, give results in tree pre-order

texts:
Given hierarchical data, for each item I need to get the sum of a column over the sub-tree rooted at the item, and I need the results in pre-order.

Example: for the data

employee_id | manager_id | salary
------------|------------|-------
          1 |       null |   1000
          2 |          1 |    100
          3 |          1 |    100
          4 |          3 |     10

I need the results

employee_id | total_salary
------------|-------------
          1 |         1210
          2 |          100
          3 |          110
          4 |           10

Oracle docs give the following example:

SELECT name, SUM(salary) "Total_Salary" FROM (
 SELECT CONNECT_BY_ROOT last_name as name, Salary
  FROM employees
  WHERE department_id = 110
  CONNECT BY PRIOR employee_id = manager_id)
  GROUP BY name;

But I also need the rows in tree pre-order, i.e each row should have its descendants immediately after it. Is there an elegant way to do this, since a simple CONNECT BY ... START WITH ... generally does give its results in pre-order?


texts:
My analytics table has 190M rows, about 150GB ins size.
Storage engine is MyISAM with

key-buffer-size=16G
myisam_sort_buffer_size=2G
myisam-max-sort-file-size=200G.

Machine has 32GB memory.
While creating 10 simple indexes (no covering, no prefix, just column names using BTREE) I noticed:

first index - 40 minutes
second index - 50 minutes
...
fifth index - 2 hours
eleventh index - 3 1/2 hours

Each index adds between 1GB and 3GB to the .myi file.
Has anybody experienced a similar increase in run-time? Is there any way to get the performance of the first index creation for the other indexes?


texts: How to bulk convert ISO8859 text in Text columns to UTF8 in nvarchar(max) columns?

texts: I've been struggling for days with a problem on a MS SQL database. Here is the situation: I have an old Open Source PHP application (Limesurvey version 1.92) running on a Windows 2008R2 server, with SQL2008R2 as RDBMS. I need to migrate the surveys in the application to a new instance, running on Linux with MySQL. The new instance is version 3, and database structure between both versions is different. The scenario I tried to use is upgrading the old version on the old server, and then exporting the surveys as export files, which I can import into the new application. It works well. Except that I'm stuck because of a problem of character encoding. The old application (web application) uses UTF-8. But the database uses only VARCHAR and TEXT columns. Therefore UTF-8 characters are stored using several bytes in these columns : é is stored as Ã© for example. When I upgrade the application, the upgrade script changes the structure of the tables: TEXT columns become NVARCHAR(MAX), but the content in the column is not converted to UTF-8. So I'm searching for a way to change the content in the NVARCHAR(MAX) columns from ISO8859 to UTF-8. The database has approx 200 tables and is 600MB.


texts: pg_restore idling during second half of restore

texts:
We're migrating from PostgreSQL hosted on our VM to Azure Database for PostgreSQL managed database. One of the first problems we hit has been time to restore database dump. It took 20-30 min on our VM but on hosted solution it takes more than 4 hours.

We are aware that there are many differences between these two environments but one thing that is confusing us the most is resource utilisation during pg_restore execution. Execution started at 10:23am and finished at 2:37pm but in period 12:46pm-2:37pm CPU and IO are both practically idling and we're having trouble understanding why.

Here's the graph of resource utilisation over that time:

Additional info:

Our VM we are moving from is: Standard F4s (4 vCPUs, 8 GB memory) with 230 GB Premium SSD storage.
Our hosted PostgreSQL environment is: Gen 5 General Purpose (2 vCores, 10 GB memory) with 122 GB Azure Premium Storage.
Database is around 20 GB in size.
We tried playing with number of jobs (2 and 4), increasing maintenance_work_mem via PGOPTIONS environment to 640 MB with no effect.

Does anyone have any ideas what might be causing this idling? For the first phase, it's clear that we need to increase IOPS limit (e.g. by purchasing more GB as IOPS limit is set my Azure to 3 IOPS/GB) to speed it up, but idling is puzzling us.


texts: How to Loadbalance a Postgres BDR Cluster

texts:
I'm planning to use BDR (by 2ndQuadrant) to setup a multi master cluster for my PostgreSQL Databases. how can i achieve real loadbalancing for read and write Operations? I dont want to do it on the application side.

Is it a good idea to combine pgpool and a BDR Cluster, given that pgpool seems to be able to Balance only Select-Queries or is there a better way to distribute load over the whole cluster?


texts: DBCC loginfo contains thousands of VLF entries with status 2

texts:
I've inherited this huge SQL Server 2000 prod database and suddenly the transaction log ran out of space.  The transaction log gets backed up every hour but I think over the weekend some existing jobs to re-index some tables grew the log file to the point where it ran out of space.  The first thing I did was backup the log (the database is in full recovery mode) and then ran dbcc sqlperf to check the log space used percentage.  It was still at over 99%.  So I did some more research and found another dbcc command, loginfo.

When I saw that output, there were almost 15K rows returned with all of status 2 and some of the CreateLSN seemed dated a while back.  I checked for any open transactions and there weren't any.

It seems like the active portion of the log file is permanently keeping the file very large forcing any new transactions to request more space.  As far as I can tell, there's no replication going on requiring the VLFs to stay active.


texts: SQL AlwaysOn Failover Due to Missed Heartbeats

texts:
I have setup SQL AlwaysOn between primary and DR data centers. Here is the setup:

Primary data center:
Server1 (Primary), Server2 (Sync Commit Secondary), Server3 (ASync Commit Secondary)

DR data center
Server4 (ASync Commit Secondary)

Data synchronization and manual failover works fine. But, sometimes, the AlwaysOn cluster automatically fails over to Sync Commit Secondary on Primary data center. Here is the error message from Failover Cluster Manager->Cluster Events:

"Cluster has missed two consecutive heartbeats for the local endpoint xx.xx.xx.yy:~3343~ connected to remote endpoint xx.xx.xx.zz:~3343~"

"Cluster has lost the UDP connection from local endpoint xx.xx.xx.yy:~3343~ connected to remote endpoint xx.xx.xx.zz:~3343~"

I had our network engineer check all connections multiple times and he confirmed everything is fine.  But he was also able to confirm (using monitoring tools) that right at the time of a failover, there is almost 2GB worth of traffic going from Primary Server to DR server. That happens every time. I had checked the times of all failovers and there is no job or process occuring that will produce 2GB worth of data. Also, this happens regardless of which server is primary.

I'd really appreciate if someone can provide some guidance. Eventhough the failover works fine, this unexpected automatic failover due to missed heartbeats are occurring often (2-3 times a month).

Thanks in advance. Have a nice day.

--Update

Here is the list of errors from the Cluster Validation Report:

Under Network Section, I see the following error messages in Red:

Validate Network Communication

Network interfaces Server4 (DR) - SAN_Team and Server1 (Primary) - SAN_Team - VLAN 20 are on the same cluster network, yet address xx.xx.xx.pp is not reachable from xx.xx.xx.yy using UDP on port 3343.

Network interfaces Server4 (DR) - SAN_Team and Server2 (Secondary) - SAN_Team - VLAN 20 are on the same cluster network, yet address xx.xx.xx.qq is not reachable from xx.xx.xx.yy using UDP on port 3343.

Network interfaces Server4 (DR) - SAN_Team and Server3 (Secondary) - SAN_Team - VLAN 20 are on the same cluster network, yet address xx.xx.xx.zz is not reachable from xx.xx.xx.yy using UDP on port 3343.

Network interfaces Server1 (Primary) - SAN_Team - VLAN 20 and Server4 (DR) - SAN_Team are on the same cluster network, yet address xx.xx.xx.yy is not reachable from xx.xx.xx.pp using UDP on port 3343.

Network interfaces Server2 (Secondary) - SAN_Team - VLAN 20 and Server4 (DR) - SAN_Team are on the same cluster network, yet address xx.xx.xx.yy is not reachable from xx.xx.xx.qq using UDP on port 3343.

Network interfaces Server3 (Secondary) - SAN_Team - VLAN 20 and Server4 (DR) - SAN_Team are on the same cluster network, yet address xx.xx.xx.yy is not reachable from xx.xx.xx.zz using UDP on port 3343.

--

Under System COnfiguration, I see the following error messages in Red:

Validate Active Directory Configuration

The servers are not all in the same organizational unit.
Connectivity to a writable domain controller from node Server4 (DR) could not be determined because of this error: Could not get domain controller name from machine Server4 (DR).

Connectivity to a writable domain controller from node Server1 (Primary) could not be determined because of this error: Could not get domain controller name from machine Server1 (Primary).

Connectivity to a writable domain controller from node Server2 (Secondary) could not be determined because of this error: Could not get domain controller name from machine Server2 (Secondary).

Connectivity to a writable domain controller from node Server3 (Secondary) could not be determined because of this error: Could not get domain controller name from machine Server3 (Secondary).

Node(s) Server4 (DR) Server1 (Primary) Server2 (Secondary) Server3 (Secondary) cannot reach a writable domain controller. Please check connectivity of these nodes to the domain controllers.


texts: Databases: are there universal datatypes?

texts:
While designing database tables (using some tool like Enterprise Architect), how do I use only 'universal' datatypes that will work with all databases when I need to switch to a different database in the future?  While switching, I would like to avoid changing datatypes of the columns that are NOT available in the new database to appropriate datatypes that are available in the new database.

In other words, are there any ANSI datatypes that will work in all the databases with the same size?  That is, similar to "ANSI SQL", are there anything like "ANSI SQL Data types"?


texts: MobiLink: What is the correct approach of presetting values inside database for remote user

texts:
When creating a remote database from inside Sybase Central I'm looking for a way to preset some values based on e.g. subscription user.

Application is using some kind of configuration table storing e.g. a base value for new id (it's historical handled inside application). To prevent collisions it's needed to set these values inside configurations table during creation of remote database.

One approach would be to run custom SQL after everything is done. But I'm wondering whether there is a way to include it into synchronisation profile of Sybase Central


texts: In Postgresql, is it possible to change the maximum number of columns a table can have?

texts:
I'm tasked with keeping a postgres table in sync with a non-postgres table. The non-postgres table has more than 1600 columns. When I try to create the table in postgres, I get:

  ERROR:  tables can have at most 1600 columns

I want to know if it is possible to raise the maximum number of columns. Is there a configuration variable I can change? Do I have to compile postgres with special options? Is there a good reason why the maximum number of columns is 1600 instead of 1700?

I have searched for this answer on Google and all answers are essentially: "you should never have a table with 1600 columns".


texts: Accessing Administrative Share Using Listener Name

texts:
Can a SQL Server AG listener name be used to access an administrative share
i.e  \\DB-listener\c$\Somefolder?

I can RDP using the listener name but can't use the listener name to access the $ shares (as mentioned above).


texts: Mysql Trigger Insert row Another Table using by Barcode

texts:
I Have 3 Tables:

1) CREATE TABLE `UrunPaketDetay`(
    `UrunPaketDetay` int NOT NULL AUTO_INCREMENT,
    `UrunPaketNo` int NULL,
    `Tarih` date NULL,
    `Barkod` varchar(14) NOT NULL,
    `Urun` varchar(50) NULL,
    `SunumSekli` varchar(50) NULL,
    `IslemeSekli` varchar(50) NULL,
    `PaketlemeSekli` varchar(50) NULL,
    `Kalibre` varchar(50) NULL,
    `Kilo` double NULL,
    `GlazeOran` varchar(50) NULL,
    `Uretici` varchar(190) NULL,
    `PaletKod` varchar(50) NULL,
    PRIMARY KEY (`UrunPaketDetay`)
)CHARACTER SET utf8;

2) CREATE TABLE `CkisEks`(
    `CikId` int NOT NULL AUTO_INCREMENT,
    `Tarih` date NULL,
    `Musteri` varchar(190) NULL,
    `TeslimatYer` varchar(50) NULL,
    `CikisSaati` time NULL,
    `AracPlakasi` varchar(18) NULL,
    `AracTel` varchar(16) NULL,
    `KonteynirNo` varchar(50) NULL,
    `PaletKod` varchar(12) NULL,
    `Kilo` double NULL,
    PRIMARY KEY (`CikId`)
)CHARACTER SET utf8;

3) CREATE TABLE `Ckis_Detay`(
    `CD_Id` int NOT NULL AUTO_INCREMENT,
    `CikId` int NULL,
    `Barkod` varchar(50) NULL,
    `Urun` varchar(50) NULL,
    `Kalibre` varchar(50) NULL,
    `Kilo` double NULL,
    `Uretici` varchar(50) NULL,
    `Musteri` varchar(190) NULL,
    `PaletKod` varchar(50) NULL,
    `Tarih` date NULL,
    PRIMARY KEY (`CD_Id`)
)CHARACTER SET utf8;

My Question:

I fill up my first table. After on 2nd table i call PaletKod row. PaletKod row autofill Kilo Row on 2nd table from 1st Table.

But i need when i fill PaletKod to my 2nd table how can i select Paletkod from first table and fill my 3rd table values (Uretici, Urun, Kilo, Kalibre ) ?

With Trigger ?


texts:
I hava a big mysql database(Innodb) about 50G, how can i import to mysql cluster.
What is the best solution to translate db, save on disk or get a amount memory?


texts: Table has 14 GB in unused space - How to shrink table size

texts:
I use the following script to gather data from my database wich is SQL Server on Azure.

-- Script to run against database to gather metrics

CREATE TABLE #SpaceUsed (name sysname,rows bigint,reserved sysname,data sysname,index_size sysname,unused sysname)

DECLARE @Counter int
DECLARE @Max int
DECLARE @Table sysname

SELECT  name, IDENTITY(int,1,1) ROWID
INTO       #TableCollection
FROM    sysobjects
WHERE xtype = 'U'
ORDER BY lower(name)

SET @Counter = 1
SET @Max = (SELECT Max(ROWID) FROM #TableCollection)

WHILE (@Counter <= @Max)
    BEGIN
        SET @Table = (SELECT name FROM #TableCollection WHERE ROWID = @Counter)
        INSERT INTO #SpaceUsed
        EXECUTE sp_spaceused @Table
        SET @Counter = @Counter + 1
    END

SELECT * FROM #SpaceUsed

DROP TABLE #TableCollection
DROP TABLE #SpaceUsed

One of my tables is 53 GB in size, with 38 GB in data, 14 GB in unused. The indexes are 11 MB in size. How do I go about reclaiming that 14 GB that is unused? As this is in Azure the extra space is costing money.

After reading various articles I have tried the following to reduce the size of the table.

DBCC DBREINDEX ('myTableName', ' ')
DBCC SHRINKDATABASE (myDatabaseName, 10);

However, after these complete, the size of the table remains unchanged. How can I go about reducing the size of the table and database in order to reclaim unused space?

UPDATE: There are 24 columns in the table with 9 of the fields being varchar of large length (> 4000) or varchar(MAX). Another 8 columns are unique-identifier types.


texts: SQL Server Database Mail failing to send emails with error Sender address rejected: Access denied

texts:
The environment is a 2 node 2008 SQL Cluster.  The first node was running fine but after failover to the second node Database Mail no longer can send emails.  I am using Basic authentication.

The full error message is: The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 0 (2012-04-10T15:26:56). Exception Message: Cannot send mails to mail server. (Transaction failed. The server response was: 5.7.1 : Sender address rejected: Access denied). )

This appears to be a SMTP issue but I have validated it is not by writing a vbscript with CDO using the same credentials as Database Mail configuration and the emails go through fine on the same box.

I have recreated the Database Mail account on the second node just to make sure the credentials are correct but that didn't fix the problem.

Does anyone know how to get this working again?


texts: Use of IN clause within a DELETE command - MySQL

texts:
I am browsing through the official MySQL documentation to learn about the DELETE clause.
This page states

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]

I can see from this WHERE, ORDER BY and LIMIT is allowed but there is no mention of IN

Therefore the following valid command

DELETE FROM WHERE column = z OR column = y OR column = x OR column = w

However I wrote the following command which execute's fine and does exactly the same as the above but looks better.(possibly executes faster too)

DELETE FROM table WHERE column IN (z,y,x,w)

Am I allowed to do this? I am wondering if maybe it works in this case but other cases would fail?
Also if this is fine should'nt the docs state it?


texts: Why MySQL query is too much slow with sending data status?

texts:
I don't know why MySQL query is too slow with status "Sending data". I read many questions and found that this status comes when you have slow disk I/O. But I have a good server hosting with 500GB Hard disk and 8GB ram on my server. I am also using indexes on the required columns on each table.

My query with left join:

Select  tbl_songs.id, tbl_songs.song_title, tbl_songs.picture,
        tbl_songs.description, tbl_songs.song_status, tbl_songs.latest,
        tbl_songs.popularity, tbl_songs.posted_date, tbl_songs.ranking_order,
        tbl_artists.id as artist_id
    from  tbl_songs
    left join  tbl_songs_artist  ON tbl_songs_artist.song_id=tbl_songs.id
    left join  tbl_artists  ON tbl_songs_artist.artist_id=tbl_artists.id
    where  MATCH (tbl_artists.artist_name)
           AGAINST ('Vectro Electro' IN BOOLEAN MODE)
    ORDER BY  tbl_songs.ids ASC
    limit  15

Here are few stats below on my server:

free -mh
             total       used       free     shared    buffers     cached
Mem:          7.8G       7.7G       123M        38M        88M       7.0G
-/+ buffers/cache:       617M       7.2G
Swap:         4.0G        33M       4.0G

top - 13:58:57 up 32 days,  9:43,  2 users,  load average: 2.07, 2.23, 1.76
Tasks: 133 total,   1 running, 132 sleeping,   0 stopped,   0 zombie
%Cpu(s): 10.6 us,  5.8 sy,  0.0 ni, 75.2 id,  8.4 wa,  0.0 hi,  0.1 si,  0.0 st
KiB Mem:   8176780 total,  8038168 used,   138612 free,     1200 buffers
KiB Swap:  4194300 total,    40028 used,  4154272 free.  7424124 cached Mem

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
 7683 mysql     20   0 1051368 186976   2984 S  65.8  2.3 161:18.73 mysqld
 4946 root      20   0  104564   3972   2884 S   0.7  0.0   0:00.08 sshd
   52 root      20   0       0      0      0 S   0.3  0.0  26:08.48 kswapd0

MySQL global variables are:

#tmpdir         = /tmp
tmpdir          = /var/mysqltmp

    max_heap_table_size = 512M
    tmp_table_size = 512M
    #sort_buffer_size = 218M
    #join_buffer_size = 5G
    #read_buffer_size = 5G
    #myisam_sort_buffer_size = 218M
    #innodb_buffer_pool_size=2G

    #
    #key_buffer             = 218M
    #max_allowed_packet     = 218M
    thread_stack            = 192K

# * Query Cache Configuration
#
query_cache_limit       = 1M
query_cache_size        = 0
query_cache_type = 0

[mysqldump]
quick
quote-names
#max_allowed_packet     = 218M

[isamchk]
#key_buffer             = 218M


texts: I have an existing table called exampletable1, now i have been given a new list of data called "medicines" it only have: male,female as its value. I'm having a problem whether to add another varchar column or to create another table and pass a foreign key to the exampletable1


texts: Left Join table with first match historically

texts:
What I want to accomplish is to join TableB to TableA where TableA.date <= TableB.date, but only the the value with the nearest max date to Table.B.date. Currently, what I get is all the previous value like this:

postgres version: PostgreSQL 9.2.24
Scripts:
CREATE TABLE public.table_a (
    id serial8 NOT NULL,
    value text NULL,
    "date" timestamp NOT NULL DEFAULT now()
);

INSERT INTO public.table_a
(value, "date")
VALUES
('Value 1', '2020-01-29'::date),
('Value 2', '2020-02-01'::date),
('Value 3', '2020-02-12'::date);

CREATE TABLE public.table_b (
    id serial8 NOT NULL,
    value text NULL,
    "date" timestamp NOT NULL DEFAULT now()
);

INSERT INTO public.table_b
(value, "date")
VALUES
('Task A', '2020-02-05'::date),
('Task B', '2020-02-15'::date);


texts: Replicating two PostgreSQL databases without using master db

texts:
I'm trying to create a simplest possible db cluster for my application and i'm not sure if that's the best way to achieve that so i'm gonna try to explain my situation the best i can.

Im basically running two instances of my app on two separate servers - one in US and one in Europe. I'm looking for a way to have one single database for both of them (i'm not interested in situation in which both apps has completely different data set in separate databases) while keeping the latency between app and db as low as possible.

It would be the best and the cheapest ("cheap" is fine in my situation because i'm not aiming to make my app super highly available, i just want the latency to be tolerateable for my users. And by cheap i mean as smallest amount of servers involved as possible.) to have two separate postgresql servers (one in US and one in EU) dedicated for each app instance BUT with the same dataset.

That's why my main question is - is that possible to have two PostgreSQL servers (one server for US and one for EU) which synchronises their contents with each other? I don't even need that small cluster to be server-down-proof (i'm not sure how that's called but i'm talking about the probably most common case and target of 99% of tutorials related to that topic on the internet - when one database is down, use the backup one)

If not, what would be the cheapest and most efficient solution to this problem?

Thanks in advance for any clues!


texts: Database Relational Design for Multiple Categories

texts:
I am building a database for participants who are grouped into studies. There's a total of 5 different studies - A, B, C, D, and E. The participants may be in more than one studies, and participants within each study has a unique "study code" with different formats depending on the study is in. (i.e. Study A's code may be 10 characters with digits and alphabets, Study B's code may be only 6 characters digits only...etc)

I know that I need a many-to-many link table, and a look-up table for the studies, but other than that I am having a hard time figuring out where to incorporate the "study code" within the tables.

Here are the fields I have so far for my tables.

Participants: participantID (primary key), name, date-of-birth...etc (background demographics)
PatientInStudies: participantID, study, study_code
LookupStudies: study(listing all the studies A, B, C..etc.)

Basically, the PatientInStudies table will look something like:

1, A, A123456789

1, B, 123456

1, C, BB23-1-52

2, A, A295890100

2, B, 123908

3, C, KG19-7-23....etc

Will the PatientInStudies Table work? Is there a better way to organize the study code within the database?

Thanks!


texts: I want to reconfigure enterprise manager and change dbcontrol http port but emca is refusing the sys password yet am sure its the correct one am using with sqlplus and even logging onto em db web app. Help me troubleshoot what the issue might be.


texts: NOLOCK hint on a table that is 100% reads?

texts:
I have tables that during production hours are not wrote to, and yet during the night they can have records written.  The few nights data is being wrote to the tables, users are not running queries against them.

We think we have some read blocking on longer running queries on these tables and some users are paying the price. We're considering adding nolock to these queries.

My general rule of thumb is no nolock.  But I'm starting to think this might be an okay use case.

I realize I could also look at READ COMMITTED snapshot isolation but I feel like it might accomplish the same thing, but would this route use the tempdb more?


texts: Query is extremely slow after ALTER and deploying of updates

texts:
I run a test server and a production server..
When I do some changes on the db structure on the test server I save all the SQL queries to later run them on the production server when I deploy the updates
Today I deployed some updates on the production server, but at least one query is now so slow that it will timeout (it takes several minutes).. It used to take 1-2 sec
On the test server the same query still runs smooth as it used to
query
SELECT dv.id,dv.client_id,b.name block_name,dv.is_ocr_pending,dv.time,dv.label,dv.is_pdf_broken,dv.is_pdf_scan,dv.is_pic,dv.file_sha1,dv.file_ext,dv.file_ext_thumb,dv.file_size,dv.file_size_original,dv.num_pages,dv.dpi,dv.ocr_confidence,dv.ocr_recall_id,dv.ocr_vatno,cv.name ocr_vatno_name,dv.ocr_vatno_country,dv.ocr_is_vatno_verified,dv.ocr_invoice_id_,dv.ocr_invoice_time,dv.ocr_invoice_time_due,dv.ocr_fi_type,dv.ocr_fi_payment_id_,dv.ocr_is_fi_payment_verified,dv.ocr_fi_creditorno,dv.ocr_bank_code,dv.ocr_bank_code_id,dv.ocr_is_bank_code_verified,dv.ocr_bank_account,dv.ocr_is_bank_account_verified,dv.ocr_bank_iban,dv.ocr_is_bank_iban_verified,dv.ocr_bank_swift,dv.ocr_bank_swift_id,dv.ocr_is_bank_swift_verified,dv.ocr_products_pattern,dv.ocr_total,dv.ocr_is_total_verified,dv.ocr_vat,dv.ocr_currency
FROM `data_voucher` dv
LEFT JOIN `block` b ON b.id=dv.block_id
LEFT JOIN `cache_vatno` cv ON cv.vatno=dv.ocr_vatno
ORDER BY dv.time DESC,dv.id DESC
LIMIT 0,25

It doesn't make sense that it timeout on one server but doesn't on the other.. When I dump the table structures the query affect the dumps are exactly the same?
If I exclude the line with LEFT JOIN cache_vatno cv ON cv.vatno=dv.ocr_vatno and remove the field cv.name ocr_vatno_name from the select clause it runs and completes as it did before the deploy
I don't know what to do?
I have now used perconas extrabackup to dump the production database and deploy it on the test server.. Now the test server has the same issues?
I can browse the table cache_vatno in phpmyadmin without problems
The table data_voucher has over 100k rows
Have tried to restart mysql
Addeing EXPLAIN to the query give this


texts: Count where previous record exists in either of two tables

texts:
I'm trying to COUNT records on one table where both the counted records and their "previous" records exist either in the same table or on another.

The table that I'm sampling has a composite PRIMARY KEY consisting of a bigint and character.  The other table has the same structure.

A "previous" record has the same character and a bigint - 1 as the counted record either on the same table or on the other table.

In pseudo:

counting_table
   character_column
   bigint_column

other_table
   character_column
   bigint_column

counting_table will never have a parallel record on other_table because the parallel record on counting_table is deleted when its primary key equivalent is inserted into other_table.

I thought I could do this with subqueries, but I can't seem to figure out how to access the outer query's data from inside the subquery.

How can my intent be implemented optimally?


texts: MySQL database datafiles in windows SystemData directory

texts:
When I create a database and some tables in MySQL Server, it makes some data files in Windows SystemData directory in C:\ProgramData\MySQL\MySQL Server 5.5\data\mydb\.

Each table has three files with these suffixes:

TableName.frm
TableName.MYD
TableName.MYI

I don't know what those exactly are.

Please explain to me what those are and what is usages?

Thanks in advance.


texts: Single-tenant to multi-tenant: restore old database to schema in new database

texts:
I have a Django app which uses Postgres as a database w/ the django-tenants package providing multitenant functionality. This is a migration from what was originally a single tenant database with just one public schema; each tenant will now have its own schema with tables created for each tenant and there is a shared public schema which has a few shared tables (e.g. the table which lists the tenants).

I want to transfer all of the data from the old database into a tenant schema in the new database. I have a database dump file from pg_dump, but I can't see any way to get data back into the tables with pg_restore or psql. Is there a way of getting the data back into the database without having to write scripts?

Hope my question isn't too confusing and I'm using the terminology correctly.

Thanks


texts: why OEM allow to connect system as normal and sys dont?

texts: In OEM I can connect with system as normal but with sys cant connect as normal. Why is that?


texts: What is the MAXDOP value for the server which doesn't have NUMA

texts:
Im using a SQL Server with the below configurations.

4 Core.
8 Logical processors.
No NUMA.
NO hyperthreading.

I read this Doc:

Recommendations and guidelines for the "max degree of parallelism" configuration option in SQL Server

Since I don't have NUMA and HyperThread.

So what could be my best MAXDOP setting?


texts:
I am new to powershell. Even I dont think I know the basics..But now I am assigned a work to do the execution of sql scripts residing in a remote server.

1) we have to connect to that particular database/server using ip and the credentials--For this I need powershell to ask user to give the required parameters(such like server/database name/ip and credentials)

2) The next step is to get the sql scripts from 3 different folders based on 3 different inputs from user--Here I need powershell to ask user to enter the parameters(Eg:a=1st folder, m=2nd folder and x=3rd folder)

3) Then the execution of the script.After the execution, then it should again ask user to continue or exit.

The 3 steps described above is my requirement. Someone please help on this..Any help would be really appreciated..

Thanks in advance..


texts:
I have a DEV Vertica database and need to create a new TST Vertica database.
During the past few months both administrators and users have had pretty-much wide open access to do what ever was needed to get things working. Thus, the internal Vertica configuration/metadata is not well tracked nor understood.
Im looking for a way to export all the internal configuration details and metadata needed to 1) Document what the current configuration is, and 2) Deploy that configuration to a new TST Vertica database.
Some of the things I am looking for are:

ACCESS/Authentication/LDAP config
Profiles/Resource/Roles/Users/Options
Object DDL, Flex tables, Schemas, Sequences
Anything else metadata related to be able to
document/reproduce  the configuration.


texts:
I have created my Oracle 12c database with database character set as AL32UTF8 and I have been told by the developers that it needs to be changed to UTF8.
National character set is UTF8.

Is it supported and feasible conversion of character set from AL32UTF8 to UTF8?


texts: What storage engine should I use for this MySQL table?

texts:
I have a table where I store all the urls from my website, and I update them from time to time when a visitor enters.

The primary key is the url itself varchar(255).

I have set it to use InnoDB but I don't know if it would be better to use MyISAM in this case for more speed.

Thanks,
Alex.


texts: How to conditionally raise an error in MySQL without stored procedure

texts:
I need to conditionally raise an error, but I can only use a simple statement and no stored procedure.

I'd like to do something like this:

select case when foo = "bar" then 1 else SIGNAL SQLSTATE 'ERROR' end;

Unfortunately SIGNAL is only usable in triggers and procedures and I have to use this within an existing application that only allows me to enter statements, but not procedures. (I only have one long line and no way to set a DELIMITER etc.)

Is there any other way to conditionally cause a runtime error ?


texts: Connection to SQL Server requires TCP communication on port 50235?

texts:
We have an SQL Server 2008 R2 with a database instance running on port 1433. 1433 is open for TCP and 1434 is open for UDP.

When connecting through Microsoft SQL Management Studio, our firewall detects TCP traffic at port 50235. Why? The SQL Server does not mention this port as far as I can tell.


texts:
I have installed wamp initailly. But now we have moved to xamp. So, I have 2 mysql folders,

C:\xampp\mysql
C:\wamp\bin\mysql\mysql\data

Now my localhost is pointing to C:\xampp\mysql. I just wanna move everything from C:\wamp\bin\mysql\mysql to C:\xampp\mysql. When I move it I get some errors.

I just need to import one database with the same permisions


texts: Comparing data types of a primary and foreign key

texts:
I'm writing a script to check SQL against a standards document before it's implemented. I'm currently trying to write a script that compares the Primary and Foreign key's data type and precision. I know I need to use:

DBA_TAB_COlUMNS
DBA_CONSTRAINTS
DBA_CONS_COLUMNS

But I can't seem to join them in a way that's going to compare the datatypes and return a result if there's a difference.


texts: Can I get Oracle to favor particular indexes?

texts:
Oracle 11.2 R2 RAC

I have a database which has many dynamic and ad hoc queries run again it.  The stats are all up to date, but often because of large numbers of complex joins and heavy parameterization, the CBO picks a less than optimal plan.

There are a couple of tables that are extremely common and if they exist in the query, its almost always a win to force a particular index rather than a full table scan (which is what it tries to pick most often).

Is there a way to artificially get the CBO to favor particular indexes without having to hint individual queries? Not a hint on the queries, but a way to make the index itself look cheaper or such?


texts: Can a hash index become inconsistent for "no reason"?

texts:
Of course, when i say no reason i mean "apparently" no reason because nothing happened on the server (forced shutdowns, data corruption, deadlocks, etc...).

I ask this because, there was an application which executed a simple select on PostgreSQL:

select * from product where barcode = '9990000088355'

Which returned nothing, but there was a record in the table with barcode = 9990000088355. There was an index specific for this field, and after reindexing it, the problem was solved and the query fetched the record.

So, is there a reason for this issue to happen, and can i do something to prevent this or at least see if there is a problem with other indices of another tables?


texts: Translating backup size to database size

texts:
I have a client who gave me a 4 gig datafile from mysqldump that I need to restore somewhere.

Is there a good rule of thumb for translating the size of a backup into the size of the database once it is restored?

I am trying to figure out if I can restore this to an existing MySQL 5.5 server, or if I need to spin up an new one.


texts: Run "USE" with linked server alternatives

texts:
I am using SQL server linked servers and find some difficulties while running queries.

Below is the actual query.

USe testdb
SELECT 'Identitiy property OFF' where COLUMNPROPERTY( OBJECT_ID('Table_1'),'column1','IsIdentity')=0

To run it to a linked server I used

Use mylinkedserver.testdb
SELECT 'Identitiy property OFF' where COLUMNPROPERTY( OBJECT_ID('Table_1'),'column1','IsIdentity')=0

But when I run this I get error as shown below
Database 'mylinkedserver' does not exist. Make sure that the name is entered correctly.

Where as I can query the table by using select * from pc91sql.testdb.dbo.Table_1 successfully.

So how to run the above query?(I think i cannot make use of "USE" with linked server.So what is the alternative

EDIT:

These things i have tried after getting nice replies.
Then i used OPENQUERY option

SELECT * FROM OPENQUERY(pc91sql,'SELECT COLUMNPROPERTY( OBJECT_ID(''testdb.dbo.Table_1''),''column1'',''IsIdentity'')');

It returned NULL. But it should have returned 1 as column1 is a identity column.

Then I checked again running my original query directly in the linked server without use command and NULL is returned . If I run same query after I mention use testdb or in SSMS after choosing database from list then it returns 1 .So it means OBJECT_ID is not able to use database name along with table name(i.e like ''testdb.dbo.Table_1'').


texts: How to group joined tables by aggregate function, specifically by average group-name type?

texts:
I'm trying to build a query selecting two columns (category types) from one table JOINED to another table-column displaying average grades as percentage number-types; here is my first attempt:

SELECT gt.grade_type_code,
       gt.description,
       gg.numeric_grade
FROM student.grade_type gt
JOIN student.grade gg ON gt.grade_type_code = gg.GRADE_TYPE_CODE;

Here is a screenshot of output for clarification:

My second attempt is closer to the expected result, sans "DESCRIPTION" column, necessitating the JOIN clause; I've also included the query and output below:

SELECT grade_type_code,
       count(grade_type_code),
       Round(avg(numeric_grade),2)
FROM student.grade
GROUP BY grade_type_code
ORDER BY GRADE_TYPE_CODE;

Lastly, here is my most recent attempt to combine the above logic:

SELECT gt.grade_type_code,
       gt.description,
       round(avg(gg.numeric_grade),2)
FROM student.grade_type gt
JOIN student.grade gg ON gt.grade_type_code = gg.GRADE_TYPE_CODE
GROUP BY gt.grade_type_code
ORDER BY gt.grade_type_code;

And the error message:

ORA-00979: not a GROUP BY expression
00979. 00000 -  "not a GROUP BY expression"
*Cause:
*Action:
Error at Line: 1 Column: 28

So essentially, I'm attempting to display the average NUMERIC_GRADE for the correspondingly grouped category types (i.e. Participation = avg percentage) but my GROUP_BY attempts invariably return errors (not sure why).  I believe my logic is directionally correct, albeit flawed; could someone please advise?  Thank you!


texts: postgresql sticking "enable_seqscan=False" in a UDF sensible?

texts:
I have a query which is taking 40 seconds to execute over the 80ms that it should if postgres where to filter off the primary key rather than doing a sequential scan. I have to force postgres to filter off the index by setting the above flag.

Having to stick this into the UDF seems like quite a hack to me, I mean it's unreasonable IMO that the query planner is chosing a seq scan over a indexed filter. Most of my SQL experience is off SQL Server, and I haven't experienced it missplaning such basic optimizations.

Edit:
To anyone interested, the query exeuction time droped from 80ms to 27 ms with 9.2, holly molley.


texts: Oracle Golden Gate - Initial data load and CDC

texts:
Im going to setup an oracle golden gate to BigData adapters. I know we can create an extract SOURCEISTABLE for the initial load. But I want to continue after initial load with CDC but without any duplicates.
Example:
time x - 10 rows
time xx - 20 rows
time xxx - 30 rows

Lets say I going to start the initial load at time x, then 10 rows will be loaded, and starting the CDC extract at time xxx then I'll miss the time xx data.
Another option
start CDC extract at time x, then 10 rows will be extracted(still the replicat is stopped)
At time xx start the initial load, it'll add time x and xx data both. so total 30. 10 duplicates.
What is the right way to do the Intial load and CDC without duplicates?


texts: Is there a reliable way to determine the order in which Postgres transactions were committed without logical decoding?

texts:
Is there a reliable way to determine the serial order in which Postgres transactions were committed without logical decoding? From what I understand pg_xact_commit_timestamp can’t necessarily be relied upon for this (and I’m not even sure I’ll be able to turn this on in this environment)? I don’t need to know the exact times transactions were committed - I just care about the order.
I should probably state the problem as perhaps there’s a better way to go about this. I have a series of tables which I am using to generate quotes. For any one of those quotes I need to be able to go back and prove that the correct quote was generated. Therefore for any tables that informed this calculation I also need to be able to reproduce their state at the time the point was generated. As this is derived data it makes the problem a bit harder.
My initial thought was I could generate these quotes using a REPEATABLE_READ READ ONLY transaction and record the txid_current_snapshot() so I know which transactions were still in-flight and therefore which snapshots to rule out from my history (I need to do this because with the current audit logs the timestamps are before the transaction was committed and therefore can not be used reliably to determine order). If there’s no way to reliably determine transaction ordering this solution falls apart.
This is what I’m using for my audit history: https://wiki.postgresql.org/wiki/Audit_trigger_91plus
Streaming the WAL via logical decoding to some upstream consumer is the eventual goal, but it’s not something I have the time to do at the moment.


texts: How to verify postgresql db restore from pg_dump file

texts:
How can I verify that my restored database is identical to my original database?

Here's what I did.  Is this a valid way to verify a restore?

# dump mydb_orig:
$ pg_dump  --cluster 8.4/my.8.4.cluster mydb_orig > mydb_orig.dump

# create mydb_restored:
$ createdb --cluster 9.1/my.9.1.cluster mydb_restored

# load mydb_orig into mydb_restored:
$ psql -q  --cluster 9.1/my.9.1.cluster -d mydb_restored -f mydb_orig.dump

# dump mydb_restored:
$ pg_dump  --cluster 9.1/my.9.1.cluster > mydb_restored.dump

# verify mydb_orig and mydb_restored are identical:
$ diff mydb_orig.dump mydb_restored.dump

# The files have the same number of bytes, but some of the rows are in a different order, so sort them:

$ sort mydb_orig.dump > sorted.orig.dump
$ sort mydb_restored.dump > sorted.restored.dump
$ diff sorted.orig.dump sorted.restored.dump
# Now they are identical


texts: AWS RDS PostgreSQL - Managing Permissions

texts:
Relatively new to AWS RDS PostgreSQL environment. We are creating a database with multiple schemas. Users are grouped by "schema_role" that have privileges to create objects in assigned schemas.

My challenge is that objects are owned by different users; this is by design. Now, the sudo “SUPERUSER” privileges in AWS RDS are limited.  When an object's created, PostgreSQL automatically assigns ownership. Object ownership confers complete control over the object – its access, integrity, and administration. By default, only the object owner may select from a table until access is granted to others. If the user does not have the necessary permission, access will be denied.

The AWS RDS "SUPERUSER" a.k.a “master user system account” does not have authority to grant the user permission to others or to select on an object created by other users, but does have ability to have object dropped. Strange!!!

We deployed an “event_trigger” (below) which is at the time of an object creation, in addition to the object ownership, it also grants all privileges to the “admin_role”.

CREATE OR REPLACE FUNCTION auto_grant_func()

RETURNS event_trigger

LANGUAGE plpgsql

AS $$
DECLARE

  obj record;

BEGIN

  FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands() WHERE command_tag='CREATE TABLE' LOOP

    EXECUTE format('GRANT ALL PRIVILEGES ON TABLE %s TO " admin_role"  WITH GRANT OPTION',obj.object_identity);
    EXECUTE format('GRANT SELECT ON TABLE %s TO "all_ro_role"',obj.object_identity);
END LOOP;

END;

$$;

Having done this, “admin_role” has more or less capability to manage all object permissions in all schemas in database.

My question is: How can I limit permissions while using this methodology?

Let’s say a schema owner A_role wants to manage his own objects within the schema?  If I make him part of “admin_role”, he inherits access to all objects in all schemas. So, how can I reduce the scope of his access to schema level only?


texts: Migrating Reports issue. The report server isn’t configured properly

texts:
I am migrating a ton of reports to a new server and am getting the
'The report server isn’t configured properly' error.
Steps I have taken:

Backed up and restored ReportServer and ReportServerTemp
Backed up and restored the Encryption Key.
Configured the Configuration Manager to look at the ReportServer.
--Not working.

One thing I noticed: The Service Account tab has 'Virtual Service Account' select. The old server has 'ReportServer' listed. I dont have an option to select Report Server.
Also, when I check the ReportServer db Users I Information_Schema and sys have a red x next to them.
Any help would be appreciated. Thanks


texts:
Possible Duplicate:
  RAM Impact on Oracle database operations

We are seeing drastic change in the amount of time taken for db operations depending on ram size.

The same content when inserted into a db running on a 4gb ram machine takes about 600% more than into a db running on a 16gb ram machine. The inserts are made through jdbc. Both the db machines were 64 bit machines and both were enterprise editions of db.

Interesting thing is that the memory consumption for the db running on 4gb machine never goes above 50-60% of the total capacity - so we do not understand why this difference.
Is there any explanation for this behavior or some more pointers to look into this?


texts:
I'm in very weird situation and i have researched a lot but not able to find anything similar to this scenarios.

Input looks something link this.

Where Each patient has Primary Dx code, Admit Dx code and Additional Dx code.

Note- Additional Dx code sometimes ends with ';' , Each Dx code is seperated by ';'.

There will be only one Primary Dx and Admit Dx column.

My output should be something link this


texts: How to keep an unique counter per row with PostgreSQL?

texts:
I need to keep an unique (per-row) revision number in a document_revisions table, where the revision number is scoped to a document, so it's not unique to the whole table, only to the related document.

I initially came up with something like:

current_rev = SELECT MAX(rev) FROM document_revisions WHERE document_id = 123;
INSERT INTO document_revisions(rev) VALUES(current_rev + 1);

But there is a race condition!

I'm trying to solve it with pg_advisory_lock, but the documentation is a bit scarce and I don't fully understand it, and I don't want to lock something by mistake.

Is the following acceptable, or am I doing it wrong, or is there a better solution?

SELECT pg_advisory_lock(123);
current_rev = SELECT MAX(rev) FROM document_revisions WHERE document_id = 123;
INSERT INTO document_revisions(rev) VALUES(current_rev + 1);
SELECT pg_advisory_unlock(123);

Shouldn't I lock the document row (key1) for a given operation (key2) instead? So that would be the proper solution:

SELECT pg_advisory_lock(id, 1) FROM documents WHERE id = 123;
current_rev = SELECT MAX(rev) FROM document_revisions WHERE document_id = 123;
INSERT INTO document_revisions(rev) VALUES(current_rev + 1);
SELECT pg_advisory_unlock(id, 1) FROM documents WHERE id = 123;

Maybe I'm not used to PostgreSQL and a SERIAL can be scoped, or maybe a sequence and nextval() would do the job better?


texts: How can I figure out the root cause of "Out of range value for column" error in MySQL

texts:
I have a console app written with C# on the top of .Net Core 2.2 framework with EntityFrameworkCore as ORM. My application runs various tasks that run every 15 minutes. Each task calls an external API server and updates my internal MySQL database (MySQL v8.16.) If the job fails for any reason, I get an email with the error.

I got the error email with the following error

  Error Message: Out of range value for column 'Value' at row 1

I am trying to figure out what Value column is causing this error and trying to figure out what query has caused this issue.

From the look of it it, MySQL does not log the invalid queries anywhere which sends me on a wild search for the problem column.

I looked up all columns that are named 'Value` in my database using the following query

SELECT table_name, Is_nullable, data_type, column_type
FROM INFORMATION_SCHEMA.COLUMNS
WHERE Table_Schema = 'my_table_name' and Column_Name = 'Value'

The above query gave me the following results

table_name    Is_nullable  data_type    column_type
field_values        NO     varchar      varchar(128)
boolean_fields      YES    tinyint      tinyint(1)
lookup_fields       YES    text         text
numeric_fields      YES    decimal      decimal(30,6)
text_fields         YES    longtext     longtext
time_fields         YES    datetime     datetime(6)
user_tokens         YES    longtext     longtext

I think if the error was caused by a column with the type of varchar/longtext I would get "Text truncated" error not "Out of range value". With that in mind, I am left with the following columns as a suspect

table_name    Is_nullable  data_type    column_type
boolean_fields      YES    tinyint      tinyint(1)
numeric_fields      YES    decimal      decimal(30,6)
time_fields         YES    datetime     datetime(6)

Furthermore, since my application is written in C# and EntityFrameworkCore is used as my ORM to interact with the database, I feel confident that both boolean_fields.Value and time_fields.Value are not causing the issue. My conclusion is because the property type is DataTime? in the TimeField entity-model where the property type is bool for my BooleanField entity-model.

With the above assumption in mind, I am down to one field that could be causing my problem.

table_name    Is_nullable  data_type    column_type
numeric_fields      YES    decimal      decimal(30,6)

I store all numeric values in to numeric_fields.Value column (ie, int, decimal and long)

The API is expected to return numeric data using the following

data_type      MinValue              MaxValue
'Int',         '-2147483648.0000',   '2147483647.0000'
'Decimal',     '-999999999999.0000,  '999999999999.0000'
'Long',        NULL,                 '9223372036854775807.0000'

I should be able to store all the above numeric in  decimal(30,6) column with no problem.

How can I identify what could be causing this problem?


texts: Restoring SQL Server database backup file for read-only access - is it possible to skip the ldf file creation?

texts:
I need to read some data from a database given by its backup file. So, I issue the RESTORE DATABASE command, which creates two files - the database MDF file and the log LDF file.

Is it possible to restore the database without creating the LDF file?