texts: Conditionally add an index to a temp table

texts:
I have a stored procedure that looks something like this:

SELECT columnlist
INTO #temptable
FROM Table
JOIN lotsofothertables
WHERE severalconditions

UPDATE anothertable
SET column = #temptable.column
FROM anothertable
JOIN #temptable
    ON anothertable.PKColumn = #temptable.PKColumn

The initial query will frequently produce just a few columns (<100) so the update is fast.  But occasionally (and these are the runs we are having problems with) it produces 1000's or even 10,000's.  Would it be reasonable to add something like this between them?

IF @@RowCount > 100
   CREATE INDEX ix_temp ON #temptable(PKColumn)

Or am I better off just creating the index on the table regardless since with so few rows the amount of time spent creating the index will be fairly small?


texts: MySQL performance of applying unique constraint single column vs combination column

texts:
I have a large MySQL InnoDB table containing around 137 million rows.

I need to add a unique constraint to the table. We have two options:

1) A combination index on three columns, one of which will be new and initially entirely NULL. The column definitions in combination the index would be:

`identifier` int(6) unsigned NOT NULL,
`value` varchar(255) NOT NULL,
`is_unique` tinyint(1) DEFAULT NULL, -- this is the new column

2) A unique index on a new column containing a unique md5 hash (calculated in the code) The definition would be:

`unique_key` CHAR(32) DEFAULT NULL

It would be easier for us to take option one as the code would be simpler, however I am wondering if there is a difference in how long it would take to apply the index in both cases. Does it take significantly longer to apply a combination unique index to a table (one column completely NULL) vs applying a unique index to a single column (completely NULL)? Or is there no difference? I know that MySQL will consider NULL values in unique indexes to not be the same (we need that behavior). If there is a big difference in the time that will affect the solution we go for.

Other Info

There are currently five indexes (including one combination index) on the table and it has four foreign key constraints to other tables.

MySQL version 5.1.69-enterprise-commercial-advanced-log.


texts:
I'm a college student and the database i'm working with is purely fictional but part of it requires me to make a query that is a join.

What I have are 3 tables each with part of the data but also needing to use two of those tables as conditions based off the main table. What I mean is I have an employee table, order table and customer table and the only thing any two of them have in common is the ID of either the employee or the customer is part of the order table. Now what i am trying to do is create a join statement that will get certain information from the employee and customer tables and only those that both the employee and the customer are also on the same line in the order table.

How should i make this type of conditional statement? Any example using the same basic scenario will work I can use that to help me build my own query.

This is what i have right now:

SELECT [Customer/Vendor_Info_local].Name_of_customer,
       [Customer/Vendor_Info_local].[Customer/VendorID],
       Employee_Info_local.Employee_Name,
       Employee_Info_local.EmployeeID
FROM Employee_Info_local
RIGHT JOIN ([Customer/Vendor_Info_local]
RIGHT JOIN Order_Information_local
   ON [Customer/Vendor_Info_local].[Customer/VendorID] = Order_Information_local.[Cusrtomer/VendorID])
   ON Employee_Info_local.EmployeeID = Order_Information_local.EmployeeID;


texts: Error granting access to sequence with PostgreSQL 8.4

texts:
I'm attempting to run this query

GRANT USAGE ON [db].users_uid_seq TO [user];

db and user are redacted (obviously).

I get this error:

ERROR:  syntax error at or near "GRANT"

I've tried a number of permutations of users, including / not including the db prefix, slightly different syntax, etc. per other stack exchange postings with no luck.

I'm using PG 8.4 with namecheap's shared hosting plan. It looks like this wouldn't be an issue with a newer version of pg but I don't have that option right now.

Any suggestions are much appreciated.


texts: Why is ORDER BY LIMIT 1 so much faster than MAX?

texts:
Here are my two queries:

select sts_in
from sta_session
where sts_user_id=2006
AND sts_sessid!='0jitkt80gg3avere03tqk4lhi6'
order by sts_in desc limit 1;

And

select max(sts_in)
from sta_session
where sts_user_id=2006
AND sts_sessid!='0jitkt80gg3avere03tqk4lhi6';

For reference, the table looks like this:

CREATE TABLE `sta_session` (
    `sta_session_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `sts_sessid` varchar(255) NOT NULL COMMENT 'PHP session ID',
    `sts_user_id` int(10) unsigned NOT NULL,
    `sts_in` bigint(20) NOT NULL,
    `sts_out` bigint(20) NOT NULL,
    `sts_ip` varchar(255) NOT NULL,
    `sts_browser_id` int(10) unsigned NOT NULL,
    PRIMARY KEY (`sta_session_id`),
    KEY `sts_sessid` (`sts_sessid`),
    KEY `sts_user_id` (`sts_user_id`),
    KEY `sts_ip` (`sts_ip`),
    KEY `fk_sta_browser_id` (`sts_browser_id`),
    KEY `idx_last_login` (`sts_user_id`,`sts_in`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

If I run explain on both the queries, the first one (with order by) uses my special index that I created just for this query, idx_last_login, but the other one uses sts_user_id.

Can the MAX query not take advantage of idx_last_login? Why not?

I know it can use that index, because I can force it, but I suspect it's ignoring the sts_in part of it and just searching by user id.

The EXPLAINs, in JSON format for easy reading.

explain select sts_in from sta_session where sts_user_id=2006 AND sts_sessid!='0jitkt80gg3avere03tqk4lhi6' order by sts_in desc limit 1;

[{
  "id": 1,
  "select_type": "SIMPLE",
  "table": "sta_session",
  "type": "ref",
  "possible_keys": "sts_sessid,sts_user_id,idx_last_login",
  "key": "idx_last_login",
  "key_len": "4",
  "ref": "const",
  "rows": 723106,
  "Extra": "Using where"
 }
]

explain select max(sts_in) from sta_session where sts_user_id=2006 AND sts_sessid!='0jitkt80gg3avere03tqk4lhi6';

[{
  "id": 1,
  "select_type": "SIMPLE",
  "table": "sta_session",
  "type": "ref",
  "possible_keys": "sts_sessid,sts_user_id,idx_last_login",
  "key": "sts_user_id",
  "key_len": "4",
  "ref": "const",
  "rows": 723107,
  "Extra": "Using where"
 }
]


texts:
I built a powershell script for collecting database information with the help of the class "Microsoft.SqlServer.Management.Smo.Server". My script
run into an error because one database has no DatabaseGuid. How could that happen?
I thought every database has a database guid?!
This database is in state "offline". I set an other database to state "offline" but the database has still a guid.

I thought each database reaceives a persistent guid, or?!

Thanks


texts: Moving SQL Server Agent Job Categories and Schedules

texts:
I want to copy a set of Job Categories and Schedules between MS SQL Servers (2012/2014).

select * FROM msdb.dbo.sysschedules

Returns all schedules.

select * FROM msdb.dbo.syscategories
WHERE category_id IN (SELECT DISTINCT category_id FROM msdb.dbo.sysjobs)

Returns all my job categories.

To add them i could use the stored procedures:

sp_add_category
sp_add_jobschedule

I can get it done with this information, but it would almost be as tedious as re-doing them manually.

How can i do this in a more effective way?


texts:
I'm trying to find a way to include table triggers when reverse-engineering the database. but I can't seem to find a way to do it.
I'm using SQLserver and .netCore 3.1 with efcore.
and each time I scaffold the dbcontext , the triggers aren't scaffolded.
Here's my command
dotnet ef dbcontext scaffold "server={server};database={database};user={user};password={password}" Microsoft.EntityFrameworkCore.SqlServer --project  "{project}" --output-dir "Models" --force


texts: Data Warehouse Design and Double Dipping

texts:
I know this may sound like a homework question, but I assure you it is not.
I am seeking for a second opinion about a lecture, usually I would go and ask the lecturer or tutors, but their answers usually leave me with more questions and sadly is a weekend and won't be able to sleep. And this time my Google techniques didn't work.

The question is about Double Dipping and it is based on one of my lectures.

Starting from this Star Schema (Lvl 1):

LVL 1 Star Schema

Which is from some olympic tables and the number of medals is my aggregated attribute.

Before going to the Double Dipping problem, we start with the Drill-Down.

The Lvl 1 is able to drill down from sport (swimming, etc) to events (4x100m relay women, etc), and its star schema (Lvl 0) would be this:

LVL 0 Star Schema

So the SportDimension was replaced with the SportEventDimension.
Because each event can have only 1 of each type of medals, then this would effectively be the Level 0, there is no real aggregation on the NumberOfMedals attribute.

In my Level 1, the aggregate function is SUM.
In my Level 0, the aggregate function is SUM. (I was told that there MUST always be an aggregate function to be considered a fact attribute, even if there will be no real aggregation).

But what if we want to Drill down even further? this time to see who was the athlete to participate in each event.

Then we could end up with the following star schema (lvl -1 (just to don't change the numeration of the previous levels)):

LVL -1 Star Schema

It looks nice... but it is wrong, it was mentioned in my lecture that this is because now we won't be able to answer questions like "how many gold medals did (country) get in 4x100m freestyle relay women".
It is also mentioned that this is because the Lvl -1 Fact Table has a different purpose, this time it has to do with athletes and not with sport events.
The result of trying to answer the previous question, would be 4 gold medals, because there are 4 participants, each one got their gold medal, but the country only was awarded 1 gold medal because it is only 1 event, and that is Double Dipping, getting more than the real value (as far as I understood).

Sample data, the number of gold medals should be 1, but now because of the Athletes dimension and the Double Dipping, we would get 4.

Double Dipping Example

To solve this, it was mentioned that we can create a multi-fact star schema. But at the end, it is mentioned that we can avoid the multi-fact and leave the level -1 as it is but mentioning that the AthleteDimension is Determinant.

Is there an actual effect just mentioning that the dimension is determinant to eliminate the Double Dipping?
My understanding for Determinant is that it is a dimension that MUST be used to give meaning to any query result. I don't quite get it how mentioning it will eliminate the Double Dipping from Level -1 and still being able to drill down from Level 0 successfully.

Can I not mention that is determinant, and use a "MAX" function when creating the report for the Level 0? (didn't rename levels, so the athletes level is still level -1, level 0 is the sport events and level 1 is the sport)

I was thinking on something like this when drilling down:
Number of medals:

Lvl -1 (Blue): SUM (although it will always be 1)
Lvl 0 (Green): MAX
Lvl 1 (Yellow): SUM

Proposed Report


texts:
How to find out more on error in merge replication on mssql server?

Error that we are getting is:

Error messages:
Error converting data type nvarchar to numeric. (Source: MSSQLServer, Error number: 8114)
Get help: http://help/8114

This kind of error is not so descriptive so we don't know where to look next. Does anybody know where to look on more descriptive error then this.

We have all objects in replication


texts: When creating primary and foreign keys is the CONSTRAINT keyword optional?

texts:
When creating a new table, sometimes examples show the primary and foreign keys defined with the CONSTRAINT keyword and other times without the CONSTRAINT keyword. Is the only reason to use the CONSTRAINT keyword to give the constraint a name? Or are there other reasons that you would choose to use or not use this keyword?
Example 1 (without the keyword):
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);

Example 2 (with the keyword):
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CONSTRAINT PK_Person PRIMARY KEY (ID)
);

Other than assigning a specified constraint name, do DBMSes see these two examples differently?  I'm thinking about SQL Server, Oracle, MySQL, and PostgreSQL in particular.


texts: Should a Sql Server be accessible through the service's virtual network name?

texts:
In a working SQL-Server failover cluster environment I can open a SQL-Server connection using the configured service's IP address independantly of the current node it is on. I wonder if the assigned virtual network name should allow to be used as well to connect? Actually it's not possible to use it.

So instead of 10.20.30.40\instance1 should I be able to connect using sqlnetwork01\instance1 as well? Do I face a configuration mistake if I can't or do I have a misunderstanding of the concepts?

Edit:
sqlnetwork01\instance1 is accessible from the same domain, my mistake was to try from a different one where it does not answer.


texts: How to prevent duplicate VARCHAR without a key limit?

texts:
I'd like to store URLs in a database column, and enforce a constraint that values must be unique. Unfortunately, MySQL has a limit on the length of index keys which means that only the first X characters of the URL gets checked for uniqueness. Thus, I've run into false positives where two different URLs triggered a constraint integration violation because the first X characters just-so-happened to be identical.

Is there a way to enforce uniqueness on a VARCHAR column without any limit on its length?

Is it possible to, say, create a non-UNIQUE index over the first X characters and then have a trigger block INSERTs if the remaining characters are identical?


texts: How could I prevent the mysql cli-/terminal client from converting/altering multiline-statements (in the process of logging)?

texts:
Is there a way to prevent the default mysql client from ?:

'removing newlines'
a.k.a. 'converting newlines to spaces'
a.k.a. 'mapping newlines to spaces'

Being more specific, with 'default mysql client' i mean the one in a terminal, command-line, shell or whatever you like to call it.

I didn't see an option for it at http://dev.mysql.com/doc/refman/5.6/en/mysql-logging.html and couldn't easily find a solution.

My guess is that it will be hard because it logs to a file ( e.g. ~/.mysql_history), like other shells do (e.g. ~/.bash_history). But I thought it couldn't hurt to ask. Here's to hoping for workarounds, hacks or something like that.


texts:
Can any one tell me when the MongoDB team will release 3.0 production release Also, will it be available for CentOS?

We are shifting our production boxes from RHEL 6 to CentOS and on the MongoDB download page 3.0.0 rc does not show CentOS in the distribution list.


texts:
Is there now, or possibly in the works a way to tell SQL Server to stay inside one NUMA node, either server wide or at the query level?   Basically the same functionality as MAXDOP, but MAXNUMA?

Right now, unless I am totally missing it, when a query goes parallel it can use all of the processors it can see.   Just wondering if there is a way to restrict it to one or two or 'x' NUMA nodes.  I may not really want my queries spread out over 80 logical processors :)

This is mostly a curiosity rather than a "help me break my broken thing" question.


texts: MySql with rollup - not giving the total. What am I doing wrong?

texts:
I'm a student and I just learned about the mysql GROUP BY and ROLLUP commands. But my WITH ROLLUP query isn't returning a total. Can someone please tell me what I'm doing wrong here?

Just like it's supposed to, it returns the total amount ordered for each product. But the last column called "GRAND TOTAL" doesn't have the total amount ordered.

Here is a screenshot -


texts: What could trigger that a login created from a certificate is not visible in metadata

texts:
I am trying to create a login and grant it rights to run and unsafe assembly but the check on server_principals does not return the login when it already exists. The script works perfectly fine on my local development server, but fails on our test servers:

My dev server: Sql Server 2008 R2 SP1 --> works ok
Remote server1: Sql Server 2005 SP4 --> error
Remote server2: Sql Server 2008 R2 RTM --> error

My user is a member of the sysadmin role.

The script will create a certificate (The real deal extracts the certificate from a .net assembly), check if the login exists and if not, it will create the login and grant unsafe assembly rights.

declare @cert_name sysname
      , @safe_cert_name sysname
      , @trust_level nvarchar(20)
      , @login_name sysname

select @cert_name = N'Cert01'
     , @trust_level = N'unsafe'
     , @login_name = QUOTENAME(@cert_name + N'Login')

select @safe_cert_name = QUOTENAME(@cert_name)

IF NOT EXISTS(SELECT * FROM sys.certificates WHERE name = @cert_name)
  BEGIN
    EXEC (N'USE master;
       CREATE CERTIFICATE ' + @safe_cert_name + N'
       ENCRYPTION BY PASSWORD = ''omg00001111;!@#$%^&''
       WITH SUBJECT = ''Test Certificate you can drop me'',
       EXPIRY_DATE = ''20120801'';')
   END

IF NOT EXISTS(SELECT * FROM master.sys.server_principals WHERE name = @cert_name + 'Login')
  BEGIN
    PRINT N'Creating login ' + @login_name + ' from certificate ' + @safe_cert_name + ''
    EXEC('USE master;
          CREATE LOGIN ' + @login_name + ' FROM CERTIFICATE ' + @safe_cert_name + ';
          GRANT ' + @trust_level + ' ASSEMBLY TO ' + @login_name + ';')
  END

The error returned by this script is the following:

Creating login [Cert01Login] from certificate [Cert01]
Msg 15025, Level 16, State 2, Line 2
The server principal 'Cert01Login' already exists.
Msg 15151, Level 16, State 1, Line 3
Cannot find the login 'Cert01Login', because it does not exist or you do not have permission.

Since my user is sysadmin on all servers I am really puzzled why this is behaving like this (unless it is a bug fixed in SQL 2008 R2 SP1).

Edit: Wrapped object names in "Quotename"

Edit: Added content of server_principals

SELECT name
  FROM sys.server_principals
 ORDER BY name

name
=========================================
##MS_AgentSigningCertificate##
##MS_PolicyEventProcessingLogin##
##MS_PolicySigningCertificate##
##MS_PolicyTsqlExecutionLogin##
##MS_SQLAuthenticatorCertificate##
##MS_SQLReplicationSigningCertificate##
##MS_SQLResourceSigningCertificate##
##MS_SmoExtendedSigningCertificate##
OURDOMAIN\ACR
...
OURDOMAIN\MYUSER
...
OURDOMAIN\ZVA
NT AUTHORITY\SYSTEM
NT SERVICE\MSSQLSERVER
NT SERVICE\SQLSERVERAGENT
SQL_Linker
archiving
bulkadmin
dbcreator
diskadmin
processadmin
public
sa
securityadmin
serveradmin
setupadmin
sysadmin

Edit: Added output of sp_helpsrvrolemember

exec sp_helpsrvrolemember 'sysadmin'

ServerRole  MemberName                MemberSID
==========  ===========               =========
sysadmin    sa                        0x01
sysadmin    NT AUTHORITY\SYSTEM       0x010100000000000512000000
sysadmin    NT SERVICE\MSSQLSERVER    0x010600000000000550000000E20F4FE7B15874E48E19026478C2DC9AC307B83E
sysadmin    NT SERVICE\SQLSERVERAGENT 0x010600000000000550000000DCA88F14B79FD47A992A3D8943F829A726066357
sysadmin    OURDOMAIN\ADMINS          0x0105000000000005150000003301161E3D17782BC63FD75B922C0000
sysadmin    OURDOMAIN\MYUSER          0x0105000000000005150000003301161E3D17782BC63FD75B8B150000


texts:
Background:
Using Business Contacts Manager 2010 with SQL 2008.
Master database is hosted through SQL and each computer connects. When the connection is stopped, each computer switches to local copy of the database. When connection is available again, the changes are synced into the master database.

Issue:
When syncing the data from local to master, an error occured and all records after the error didn't trasfer the changes and erased the changes from the local database.
Is there any way to retrieve the data that was changed only on the local machine before the sync operation occured?

A transaction log LDF file of the local database copy is the only thing that may have that data in it but didn't have any luck retrieving the data so far. No local backups were made prior to the incident. The only way I believe this can be done is through the LDF file unless there is a way to roll back the local copy to a previous date even without a backup log file being created.

Notes: The last backup of the local machine database copy was from October 2011. The master database from the server was backed up much more recently but the information that I'm trying to retrieve is in the "SQL Serve Database Transactions Log File" aka the .LDF file.

So if I restore the local database to the backup from October 2011 could I redo all changes since and up until when the data was lost using the .LDF file? I don't think any recovery mode was ever set or changed from the default settings. Also, the database is not very large, only 500MB, and the data that was lost was only from a notes field and a call-date field for around 100 contacts so maybe its not worth all that work.


texts: Error occur during Oracle and Sql Server Integration

texts:
I have developed a SQL Server Integration Services package pulling data from Oracle 11g and storing in a SQL Server 2008 database. Everything works as expected, but when I schedule a sqlserver job to run the SSIS package it returns the following error:

  Error: System.InvalidOperationException: Attempt to load Oracle client libraries threw BadImageFormatException. This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed. ---> System.BadImageFormatException: An attempt was made to load a program with an incorrect format. (Exception from HRESULT: 0x8007000B) at System.Data.Common.UnsafeNativeMethods.OCILobCopy2(IntPtr svchp, IntPtr errhp, IntPtr dst_locp, IntPtr src_locp, UInt64 amount, UInt64 dst_offset, UInt64 src_offset) at System.Data.OracleClient.OCI.DetermineClientVersion() --- End of inner exception stack trace --- at System.Data.OracleClient.OCI.DetermineClientVersion() at System.Data.OracleClient.OracleInternalConnection.OpenOnLocalTransaction(String userName, String password, String serverName, Boolean integratedSecurity, Boolean unicode, Boolean omitOracleConnectionName) at System.Data.OracleClient.OracleInternalConnection..ctor(OracleConnectionString connectionOptions) at System.Data.OracleClient.OracleConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.OracleClient.OracleConnection.Open() at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction)

Please note this error occurs only while running the package as a sqlserver job. If I run the package via Integrated Services in SQL Server Management Studio then I don't get any error.

I have even installed both 64-bit and 34-bit Oracle driver but no luck :(

Please provide inputs.


texts: Why not create ColumnStore index on every single table?

texts:
As ColumnStore index improves performance for data warehouse databases, and also since SQL 2104, it is updatable too, why not creating ColumnStore index on every single table?

Edit: In SQL 2014 we cannot have any other index if there is a CLUSTERED ColumnStore index. This limitation is eliminated in SQL 2016 so my question can be more relevant for SQL 2016.


texts: How does Postgres handle integer data of different sizes in the same column?

texts:
If I have a bigint column and in one row store 1 and in another store 999999999999, will these take up different amounts of space on disk?

Will Postgres have an easier time doing queries and calculations with the smaller data?

The motivation for my question is that I have a lot of data in this form: 1000000306429071. I'm wondering if it will be valuable to strip the 1 and leading 0s.


texts:
For data integrity ON DELETE CASCADE is necessary but it's really dangerous when a delete affects like a Chain Reaction!

I know one way to prevent this is to make a pseudo delete by putting a "Delete" flag on in all tables where the record is being deleted from. But that's a very old style and clumsy way. How can I optimize this or is there any way to do it from inside database (SQL Code) ?


texts: Help my database isn't performing fast enough! 100M Merge with 6M need < 1 hour!

texts:
I have a server right now receiving more raw data files in 1 hour then I can upsert (insert -> merge) in an hour.

I have a table with 100M (rounded up) rows. Table is currently MyISAM. The table has 1000 columns mostly boolean and a few varchar.

Currently the fastest way i've found to get the information into my DB until now was:

Process raw data into CSV files.
Load Data In File to rawData Table.
Insert rawData table into Table1. (on dupe key do my function)
Truncate rawData
Repeat.
Worked fine until im merging 6M+ Rows into 100M rows and expecting it to take under an hour.

I got 16G of ram so I set my Key_Buffer_Pool to 6G.
I have my query cache pool to 16M
I have my query cache limit to 10M
I would just replace the information however it has to be an Upsert, Update the fields that are true if exists and insert if it does not.

Things im looking into atm; - Possibly switching server table to InnoDB? |-> Not sure about the performance, as the insert into an empty table is fine, its the merge that's slow.

Maybe allowing more table cache? Or even Query Cache?
mysql sql mysqli innodb myisam

Merge Code:

b.3_InMarket = (b.3_InMarket OR r.3_InMarket),

To compare my 2 bool columns.

Update

Ok I set Raid0
Changed my query to Lock Write on tables when inserting
When importing csv im disabling keys then re-enabling them  before upsert.
Changed concurrent_insert to 2


texts:
I have three tables which has the same schema. all of them has 10000000+ rows and has unique data as well as duplicate keys but with a datetime column can differentiate them and get the newest row.

I want to create a one combine table with all the data in my three tables updated with the newest records for duplicate values.

Suggest me a method to achieve this.


texts: Is the CPU performance relevant for a database server?

texts:
This is a purely theoretical question. Let's say I have an application deployed on multiple servers.

A load balancer,
Multiple/scalable applications servers
A (single) database server (for the moment)

On the two first parts, I do know what to look for. But what about the database server? What kind of hardware should I look for?

Is CPU frequency relevant for a database server?
Are multiple core CPUs relevant?
Is RAM more important than CPU?

PS: Supposing the chosen database is MySQL or PostgreSQL.


texts: Massive peer-to-peer replication topology?

texts:
We have a client that's geographically distributed in remote communities, with somewhat unreliable network/internet connectivity between each of the physical locations. There is a single central location, and 52 satellite locations.

Normally, our application is deployed as a central solution where there is only a single database, and all locations connect to the one database. The data itself is partitioned by a location column in most of the tables (some data is centralized), and the application operates such that touching data at one physical location does not touch data at another physical location, including the centralized data (at least, we're pretty sure it doesn't -- if it does, it's likely a bug). Our client would like to do centralized reporting (which we normally support), and synchronization of several centralized configuration tables across the enterprise (same, because there's normally only one copy of the tables).

Every location has its own data center, and is licenced for SQL Server 2008 R2 Enterprise.

The goal is to deploy our application such that broken network/internet connectivity must not prevent local read/write operations, as our application is mission-critical.

My first thought was to use a giant peer-to-peer topology that replicates every object in the database. I haven't been able to find any documentation or references of any installation that's scaled out this much using this method, so I have no idea if it's technically feasible, and if so, what hardware is going to be required. Would this work with a centralized distribution database (for ease of management)? Is this even a good idea at all? How are database schema changes to be deployed? Does index maintenance generate a huge amount of network overhead?

There's also the option of creating a roll-our-own solution, which I figured would involve log shipping separate copies of the database (each non-central location would only contain its partitioned portion of the data) to the central location, and then using a merging tool (which we already have) to create a reporting database. Configuration table changes would be pushed out by some other mechanism, possibly merge replication, possibly a homebrew solution.

I considered and tested using merge replication on the entire database, but we require quite a lot of database and application changes/redesign/etc. to make this work correctly, so I've ruled that out already due to time constraints. (Thoughts on this?)

What I'm looking for here is some guidance from people with more experience. Obviously, planning for this is critical. Am I on the right path? Are there other options? (URLT?)


texts: How to set the deadlock priority for a maintenance task created trough the graphical interface

texts:
How can I change the deadlock priority for a Sql Server 2012 Enterprise maintenance task that is created and configured using the GUI wizard?

I.e. There is a maintenance plan with an "rebuild index of all tables of XY" or "reorganize index of all tables of XY" subtask, which was created using the maintenance plan wizard of Sql Server Management Studio.

I know I can change the deadlock priority for a transaction by executing SET DEADLOCK_PRIORITY LOW, but where would I insert such a command?

If at all possible I would like to avoid abandoning the GUI subtasks in favor of some sort of stored procedure or script. Noone here, including me, has the expertise to maintain such a script.

From my research so far, it seems like setting the priority for a gui task is simply not possible. If this is true, I still would appreciate a definitive answer confirming this, so I can stop searching.


texts: How can I handle delayed records a month or older on a table clustered on time?

texts:
I have a two part question really for anyone working with data warehousing where they have large fact tables.

Question 1

Lets say you have a table that has 500 million or more records in it that is clustered on time. You are only posting incremental records for the last 24 hours on a daily basis to this table.

How do you handle inserting delayed records that are a month or older to that table? Would you do nothing unless the insert caused a lot of fragmentation or would you attempt to drop the indexes, insert and rebuild?

I do not have enterprise edition available to me for table partitioning.

Question 2

If you have a fact table that is growing large like the above example, would it be wise to split the fact table up into multiple tables or would it be better to look towards adding additional files to the filegroup of said table if table partitioning is not an option?

Thanks in advance. I'm dealing with some large growth and trying to approach it the right way.


texts: How to avoid duplicate entries with SELECT statement?


texts: Materialized view with FAST refresh on remote table: How to include a GEOMETRY column?

texts:
I want to create a fast refresh materialized view (18c) on a remote table. The MV would also have a GEOMETRY column.
Options for the GEOMETRY column datatype include:

ESRI's proprietary implementation of ST_GEOMETRY (user-defined datatype; is an 'object' datatype)
Oracle's SDO_GEOMETRY datatype

To start, I can successfully create a fast refresh MV without a GEOMETRY column:
create materialized view log on maximo.workorder with primary key;  --remote table
grant select maximo.mlog$_workorder to schema_for_dblink; --I've given the dblink access to everything in this schema

create materialized view my_gis_schema.wo_mv
build immediate
refresh fast
start with sysdate next sysdate + (15/(60*60*24))
as
select
    cast(workorderid as number(38,0)) as objectid,
    wonum,
    status,
    --other fields
    longitudex,
    latitudey
from
    maximo.workorder@my_dblink

The MV above works, but I want to store the XY coordinates from the remote table in a GEOMETRY column in the MV (right now, the coordinates are stored in number columns, not a geometry column).
Unfortunately,  my options for the GEOMETRY column in an MV seem pretty limited:

Oracle doesn’t seem to support ESRI's ST_GEOMETRY datatype in MVs (more info here and here).

The SQL would be: sde.st_geometry(longitudex,latitudey,null,null, 26917 ) as shape

Additionally, Oracle doesn't seem to support SDO_GEOMETRY in MVs with the fast refresh option on a remote table: ORA-12015: cannot create a fast refresh materialized view from a complex query

The SQL would be: sdo_geometry(2001, 26917, sdo_point_type(longitudex,latitudey, null), null, null) as shape

Question:
Is there a way to include a GEOMETRY column in a materialized view on a remote table, using the fast refresh option?


texts: SQL errors Microsoft.ACE.OLEDB.12.0 on dev box, but works on server

texts:
I'm inserting into a .xls spreadsheet from TSql code.  Runs fine as an Agent job and on command from the server, but not on my dev box.

Both complex and simple commands like:

Insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\test.xls;', 'SELECT * FROM [Sheet1$]')
SELECT 'hello'

Select * from OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\test.xls;', 'SELECT * FROM [Sheet1$]')

SQL Server 2008 R2 64bit installed on Windows Server 2008 R2. SSMS installed, these commands work there.

When trying to run the same commands from another computer (my dev box), I can't do it, even though I've tried the suggestions below and elsewhere. I have Windows 7 64bit, SSMS 2014 installed here, but had the same issues with 2012 and 2008 R2.

When I run this command in SSMS on my dev box, does it send the entire command to the SQL server? Which security context does it run in? I really want to be able to develop these kinds of stored procs on my dev box, right now this is the only reason I ever RDP to my server, which I'd rather not do.

I've tried the steps listed here: [http://www.codeproject.com/Questions/692004/The-OLE-DB-provider-Microsoft-ACE-OLEDB-for-l] to no help.

EDIT: The error I'm getting is:
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

UPDATE: Turns out installing the 2007 Access Engine allows me to work with Microsoft.ACE.OLEDB.12.0 from my dev box, but only when they're stored directly on the SQL Server.  My files are stored on a separate computer (accessed via share), and I still cannot work with those files.  Looks like a double-hop permissions issue, but I still can't find it.

EDIT: I can do these queries from my dev box when logged in as SA, but not as myself.


texts: Max Price Using Multiple Tables and Columns

texts:
The below code is working for me to pull out the max Avg Price, but I need to add another couple of fields from the dbo.TreatmentEvent table, when I add another column it stops giving me the max and instead just starts listing out all values.
SELECT Max_AvgPrice = MAX(pav.AvgPrice),
       te.a_ID_Treatment
  FROM dbo.TreatmentEvent           te
  LEFT JOIN
       dbo.dim_Procedure_Codes      pco ON te.Code_Insurance_1 = pco.a_IDPK_ProcedureCode
  LEFT JOIN
       dbo.dim_Procedure_Category   pca ON pco.a_ID_ProcedureCategory = pca.a_IDPK_ProcedureCategory
  LEFT JOIN
       dbo.dim_Procedure_AvgPrice   pav  ON pco.a_ID_ProcedureGrouping = pav.a_ID_ProcedureGrouping
 GROUP BY
       te.a_ID_Treatment,
       YEAR(te.TreatmentEventStartDate),
       pca.a_IDPK_ProcedureCategory,
       te.a_ID_TreatmentEventStatus
HAVING (YEAR(te.TreatmentEventStartDate) = YEAR(GETDATE())
        OR YEAR(te.TreatmentEventStartDate) = YEAR(GETDATE()) - 1)
       AND (pca.a_IDPK_ProcedureCategory = 1)
       AND (te.a_ID_TreatmentEventStatus = 3);

When I add in other columns this is an example the result;
a_ID_Treatment     a__IDPK_TreatmentEvent     Code_Insurance_1     Max_AvgPrice
454646             432023                     455                  1030
454646             431168                     194                  719

What I need to get is just the first line with the AvgPrice of 1030, but I need to see the other columns too that apply to that line.
Anyone have any suggestions?
Thanks


texts:
I have a situation where incoming rows (src) need to be matched to rows in a target table (tgt). However, each target row can only be matched once for all the incoming source rows. Example SQL follows:

drop table if exists src;
create table src(id int not null, join_key text not null);
insert into src(id, join_key) values
(1,'X'),
(2,'X'),
(3,'X'),
(4,'Y'),
(5,'Y')
;

drop table if exists tgt;
create table tgt(id int not null, join_key text not null);
insert into tgt(id, join_key) values
(10,'X'),
(20,'X'),
(40,'Y')
;

with a as (
    select s.id as src_id,
           s.join_key,
           t.id as tgt_id
    from src s
             left join tgt t on s.join_key = t.join_key
),b as (
    select *,
           row_number() over (partition by src_id order by tgt_id) as rw
    from a
)
     select src_id, tgt_id from b
 where rw = 1
;

This gives:

1,10
2,10
3,10
4,40
5,40

But the values should be:

1,10
2,20
3,null
4,40
5,null

How can I do this?


texts: What will be the approximate downtime for indexing a column in psql

texts:
I have an Elixir application that uses Postgres Db and I want to index a field with a row count of 54K. There are few NULL values as well.
I use Ecto.Migration
def change do
   create(index(:rules_engine_test_cases, [:rule_set_version_id]))
end

Will there be any downtime in this case?


texts: Storing draft version without any constraint checks

texts:
If a system allows users to save draft (not versioning but kind of like partial save) of their work in database, I can think of 3 ways.

I see similar questions being asked here already but I could not find any with numbers showing performance of each approach. So just wanted to check if someone has evaluated approaches of this sort and already knows performance of each.

Create duplicate tables: We can have 2 copies of each affected table. One with all constraints and one without. Drafts go into one without checks and final version (with all mandatory data) goes in to another. Application code will then look into both tables and do whatever they do with it. This violates DRI and also creates 2 entities on UI for same thing. Plus they are querying 2 tables where they should ideally be querying one.
Same approach as #1 but create a view which shows all records together. Here, application code will only deal with view. This will again violate DRI and also create an overhead of recreation of views at every insert/update.
Have check constraints on all mandatory table along with a flag to tell if this is partial or complete save. This makes life a bit easy but might cause performance impact because check constraint will be evaluated all the time.

This is for a rather small database with 50 odd tables where biggest one would have about 15 columns. There are few tables which also save blob (~500 KB max).

Any suggestions (apart from trying it out myself, of course)?


texts: Modelling a relationship which has multiple one-to-many parents

texts:
I am attempting to model a relationship which could have two parents, one of them being optional, and the other being mandatory. The design should have the following rules,

A House must exist as the top level parent
A Bedroom must have a house as a parent
A House can have many Bedrooms
A House can have many Photos of the house
A Photo could also optionally be linked to a Bedroom, i.e. it is a photo of a bedroom in a house
A User should only be able to access a House and any child (Bedroom and Photo resources) that are owned by them (through the UserID field in House table)

My current design looks like this:

    +---------+         +-----------+
    | House   |         | Bedroom   |
    +---------+         +-----------+
+---> HouseID <---+     | BedroomID <----+
|   | UserID  |   +-----+ HouseID   |    |
|   | ...     |         | ...       |    |
|   +---------+         +-----------+    |
|                                        |
|                                        |
|           +-----------+                |
|           | Photo     |                |
|           +-----------+                |
|           | PhotoID   |                |
+-----------+ HouseID   |   OPTIONAL     |
            | BedroomID +----------------+
            | ...       |
            +-----------+

I currently enforce that a Photo must be linked to a House through the FK, so I can perform a JOIN to get the original UserID, as I know this key will already exist, but it feels like this is not the right way to go about it.

I ideally want to return Photos that are only linked to the House when querying data about the House, and then only Photos linked to a Bedroom when querying data about a Bedroom. I know this is quite easy to do via JOINs, but it makes me question whether this is the right design, as it seems to be more "working around the design" than fixing the root problem.

Would it be more useful to use mapping tables here? Or any further ideas on how to improve it?


texts: Outputting the results of two select queries on to a single table in Oracle SQL

texts:
So after constructing a database for an "online auction website" I need to write a query that takes the average price of two different types of products and displays them in a table with the name of the product type next to it EG:

Gender   Averageprice
Mens     £65.34
Womens   £86.42

The price of the items and the gender of the clothing are in two seperate tables as a parent child relationship ie clothing is a subtype of item.

DESC TABLE ITEM
Name    Null     Type
------- -------- ------------
ITEM_ID NOT NULL NUMBER(38)
PRICE   NOT NULL NUMBER(12,6)

DESC TABLE ITEM_CLOTHING
Name         Null     Type
------------ -------- ------------
ITEM_ID      NOT NULL NUMBER(38)
CLOTHES_TYPE NOT NULL VARCHAR2(10)

I can list the names of the clothing types using...

SELECT DISTINCT CLOTHES_TYPE
FROM ITEM_CLOTHING;

and I can list the average price of the two different clothing types using...

select sum(PRICE) / count(*)
FROM ITEM,ITEM_CLOTHING
WHERE ITEM.ITEM_ID = ITEM_CLOTHING.ITEM_ID AND ITEM_CLOTHING.CLOTHES_TYPE = 'Mens'
UNION ALL
select sum(PRICE) / count(*)
FROM ITEM,ITEM_CLOTHING
WHERE ITEM.ITEM_ID = ITEM_CLOTHING.ITEM_ID AND ITEM_CLOTHING.CLOTHES_TYPE = 'Womens';

I just can't work out how to put it all together in to a single table as shown above.  Could you please help?


texts: Killed DbccFilesCompact found running for days

texts: There were 2 running, actually, the first one I killed went away quickly. The second is in suspended status, from sys.dm_exec_requests the percent_complete doesn't change (88.87) and the estimated_completion_time keeps going up. What can I do? There is no room left on the one mdf file drive, is that the problem? This is why my coworker started the shrink (he's on vacation now, long story why they started doing the shrinks, databases is being moved to Hadoop, and I'm new (3rd week)). Please advise.. thanks!


texts: InnoDB Failing to start / MySQL not loading plugins

texts:
I installed MariaDB on my system (Debian 9), however, upon running sudo service mysql start, I get these errors:
Sep 22 12:32:37 bremea mariadbd[30151]: 2020-09-22 12:32:37 0 [Note] /usr/sbin/mariadbd (mysqld 10.5.5-MariaDB-1:10.5.5+maria~stretch) starting as process 30151 ...
Sep 22 12:32:37 bremea mariadbd[30151]: 2020-09-22 12:32:37 0 [Warning] Could not increase number of max_open_files to more than 16384 (request: 32184)
Sep 22 12:32:37 bremea mariadbd[30151]: 2020-09-22 12:32:37 0 [Note] InnoDB: Using Linux native AIO
Sep 22 12:32:37 bremea mariadbd[30151]: 2020-09-22 12:32:37 0 [Note] InnoDB: Uses event mutexes
Sep 22 12:32:37 bremea mariadbd[30151]: 2020-09-22 12:32:37 0 [Note] InnoDB: Compressed tables use zlib 1.2.8
Sep 22 12:32:37 bremea mariadbd[30151]: 2020-09-22 12:32:37 0 [Note] InnoDB: Number of pools: 1
Sep 22 12:32:37 bremea mariadbd[30151]: 2020-09-22 12:32:37 0 [Note] InnoDB: Using SSE4.2 crc32 instructions
Sep 22 12:32:37 bremea mariadbd[30151]: 2020-09-22 12:32:37 0 [Note] mariadbd: O_TMPFILE is not supported on /tmp (disabling future attempts)
Sep 22 12:32:37 bremea mariadbd[30151]: 2020-09-22 12:32:37 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728
Sep 22 12:32:37 bremea mariadbd[30151]: 2020-09-22 12:32:37 0 [Note] InnoDB: Completed initialization of buffer pool
Sep 22 12:32:37 bremea mariadbd[30151]: 2020-09-22 12:32:37 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
Sep 22 12:32:37 bremea mariadbd[30151]: 2020-09-22 12:32:37 0 [ERROR] InnoDB: Invalid flags 0x4800 in ./ibdata1
Sep 22 12:32:37 bremea mariadbd[30151]: 2020-09-22 12:32:37 0 [ERROR] InnoDB: Plugin initialization aborted with error Data structure corruption
Sep 22 12:32:37 bremea mariadbd[30151]: 2020-09-22 12:32:37 0 [Note] InnoDB: Starting shutdown...
Sep 22 12:32:37 bremea mariadbd[30151]: 2020-09-22 12:32:37 0 [ERROR] Plugin 'InnoDB' init function returned error.
Sep 22 12:32:37 bremea mariadbd[30151]: 2020-09-22 12:32:37 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
Sep 22 12:32:37 bremea mariadbd[30151]: 2020-09-22 12:32:37 0 [Note] Plugin 'FEEDBACK' is disabled.
Sep 22 12:32:37 bremea mariadbd[30151]: 2020-09-22 12:32:37 0 [ERROR] Could not open mysql.plugin table: "Table 'mysql.plugin' doesn't exist". Some plugins may be not loaded
Sep 22 12:32:37 bremea mariadbd[30151]: 2020-09-22 12:32:37 0 [ERROR] Unknown/unsupported storage engine: InnoDB
Sep 22 12:32:37 bremea mariadbd[30151]: 2020-09-22 12:32:37 0 [ERROR] Aborting
Sep 22 12:32:37 bremea systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE
Sep 22 12:32:37 bremea systemd[1]: Failed to start MariaDB 10.5.5 database server.

I assume this is an error with InnoDB, but I may be wrong. I tried uninstalling/reinstalling mysql but that didn't work either. Can anyone steer me in the right direction?


texts: Index selection in MongoDB 3.0 for $or query


texts: I have Fragmented Index on a empty table!

texts:
I ran the following query on my database to identify the fragmented indexes. However I surprised when saw the Indexes belong to some empty tables among the highly fragmented indexes! How can it be possible? I assume if there is no data in a table, the index should be blank and can not be fragmented.

    SELECT
    a.index_id,
    name,
    avg_fragmentation_in_percent
FROM
    sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, N'Limited') AS a
    JOIN sys.indexes AS b
        ON a.object_id = b.object_id
        AND a.index_id = b.index_id

Could someone give me an explanation?

Thank you in advance.

Nazila


texts:
This question contains no concrete programming question, i would only like to hear some ideas, on how it would be appropriate to design my database.
So recently I started designing an application where the main goal is the following:

There are users
The users can create reports (they some get points for creating one)
They can provide additional data (additional points)
They can view others reports
They can unlock additional data about other peoples records, using
their own points

The point of my question is: How to store and/or calculate the users current points?

1. First method
The schema, and points-calculating method I first thought of looks like this:

The users (can) have reports, the reports (can) have report details, which are connected to a report detail type, which contains the information about how much points it costs to unlock, or to create a detail from a given type. The user also (can) have transactions which contains the unlocks by the user.
From this schema i could create a query which returns, how much score the user have created, and how much he/she has spent with transactions. I could the substract the two, and i have the current score.
The problem with this: If the score for one report detail type gets modified the score for all users will be modified. Which i don't want to.

2. Second method
The second idea i had is to simply store the users points in a column in the users table, and whenever he/she tries to create/unlock something, update it. The schema could be identical, but there would be an extra column in the users table. For some reason it feels really unprofessional, and it also feels like, that i would store data, where it doesn't belong.

3. ???

I would really appreciate any ideas, on which one of these would be better and why! If I'm started thinking about the problem in a completely wrong manner, I also would like to know about a better solution! Thanks for any help!


texts: How to select Data from ODBC Source in MySQL

texts:
I try to select data from MSSQL in MySQL over straight way. I want to store the result in a view.
What did I do now:
Add the MSSQL Server as a ODBC Datasource.


texts: Sum of integers between two rows in Hive

texts:
Suppose I have a Hive table that looks like this:

 a    b
41   77
8    32
31   76

I would like to have another column say c which applies a function that loops through all integers between columns a and b. For example, c could contain the sum of all integers between a and b. Is there a way to phrase my Hive query?

Edit: I'm using Hive 2.1.1-mapr-1803 version.


texts: How to set default innodb_buffer_pool_size before mysqldump and set it back to 1600M after mysqldump?

texts:
I want to make backups every day automatically. It was fine when database was 700-800Mb, but now database (dumped sql file) is 1.5G and when using mysqldump it errors on random table with 2013 error.

When I comment out my setting of innodb_buffer_pool_size, mysqldump works perfectly.

Is there nice way to set to defaults innodb_buffer_pool_size before mysqldump and then back to 1600M?

p.s. I'm using mysqldump as following

mysqldump --max_allowed_packet=500M --net_buffer_length=548576 --single-transaction?


texts: Performance tuning across two tables using indexes?

texts:
Let say I have two tables and a SQL query like this

SELECT table_a.*, table_b.*
FROM table_a, table_b
WHERE table_a.id = ?
AND table_b.col_x = table_a.col_x
AND table_b.id = table_a.id;

Would there be any performance improvement by rewriting the SQL query to JOIN

SELECT table_a.*, table_b.*
FROM table_a
JOIN table_b on (table_b.id = table_a.id AND table_b.col_x = table_a.col_x)
WHERE table_a.id = ?;

Assuming that the tables have these current INDEXES

--table_a
create index table_a_index_z_id on table_a (col_z, id);

--table_a
create index table_a_index_x on table_a (col_x);

--table_b
create index table_b_index_id on table_b (id);

What extra indexes if any would improve the performance of the queries above?

EDIT

The primary keys for the tables are

alter table table_a add (constraint table_a_pk primary key (id, col_b, col_x))
// there is no PK on table_b


texts:
Oracle 12c has introduced Oracle home user in its installation process. I have searched and read some articles about Oracle home user. (Please see screenshot below)

My questions are:

If I use create new windows user, I typed username and password here, after the installation, I do not see a windows user is created in my windows system(control panel -> manage account). Do I need to create a windows user with the same name and password in order to use the database? Or I do not need to create anything, it is just a windows user hidden somewhere in my system. And what Did this create new windows user really do.
Oracle says use this create new windows user, the user will not have login privilege. What does this mean, it has no login privilege for windows? or for Oracle database.
First time I tried to install Oracle database, it was hanging at 88%. I uninstalled and deleted everything I know, then I started to install it again, this time if I use "Create new windows user", and use the same user name, it told me Specified User is already exists. As I said before, there is no such user in my windows.(control panel -> manage account). Now if I use a new one, it will work. Is there any way I can find where was the previous user stored? I checked the inventory.xml, its not in there.


texts: Select unique records from multiple records based on max date

texts:
I have table like below

+----+----------+-----+-------------+--------+
| ID |   ADDR   | IND | update_Date | Sq_NBR |
+----+----------+-----+-------------+--------+
|  1 | PHYSICAL | Y   | 11-Oct      |   1293 |
|  1 | BILL     | Y   | 10-Oct      |   1357 |
|  1 | BILL     | N   | 10-Sep      |  13567 |
|  1 | PHYSICAL | Y   | 10-Oct      |   8403 |
|  1 | BILL     | Y   | 6-Oct       |    135 |
|  2 | PHYSICAL | Y   | 9-Oct       |   5333 |
|  2 | BILL     | N   | 8-Aug       |    657 |
|  2 | BILL     | Y   | 8-Oct       |   3673 |
|  2 | BILL     | Y   | 10-Oct      |   2574 |
|  3 | BILL     | Y   | 11-Oct      |  57923 |
|  3 | BILL     | Y   | 11-Oct      |  29384 |
+----+----------+-----+-------------+--------+

need to check below multiple conditions before populating the data

If record has a combination of duplicate id, addr and Ind, then select the max(update_date) from that record.
If record has a combination of duplicate id, addr, Ind and update_Date, select only one record from the duplicate records (It can be any record).

Below was the expected result.

Can anyone plug me some ideas to achieve this.

here is the sqlfiddle:  http://sqlfiddle.com/#!4/d0098/1

+----+----------+-----+-------------+--------+
| ID |   ADDR   | IND | Update_Date | Sq_NBR |
+----+----------+-----+-------------+--------+
|  1 | PHYSICAL | Y   | 11-Oct      |   1293 |
|  1 | BILL     | Y   | 10-Oct      |   1357 |
|  1 | BILL     | N   | 10-Sep      |  13567 |
|  2 | PHYSICAL | Y   | 9-Oct       |   5333 |
|  2 | BILL     | N   | 8-Aug       |    657 |
|  2 | BILL     | Y   | 10-Oct      |   2574 |
|  3 | BILL     | Y   | 11-Oct      |  29384 |
+----+----------+-----+-------------+--------+


texts: Difficulty in moving a SQL Server database

texts:
I'm trying to upgrade from SQL Server 2008 R2 Express to Developer.

My plan is to remove the Express edition, then install the Developer edition. I have one database I need to bring over to the new installation.

To prep, I made physical copies of the data and log files to the desktop, as well as performed a full backup to the desktop. I then detached the database in Management Studio.

To get a restore under my belt before removing Express, I went to physically move the .mdf and log files in windows explorer after detaching, but I don't see the files.

I thought perhaps there was no need, I could restore from the backup (again, still with Express), I get the error that there is insufficient space. And interestingly, I don't see the backup file on the desktop. I am sure I specified it carefully. I tried plan B, to attach the .mdf file that I copied to the desktop, and I get

  CREATE FILE encountered operating system error 2 (The system cannot
  find the file specified).

Any help here would be huge. Thanks.


texts: I would like to ensure that I have a backed up copy of every job on my SQL server 2008 R2 instance. Any suggestions?


texts: How to correctly interpret index usages from EXPLAIN of mysql?

texts:
I've exported the results of an EXPLAIN run on a query. What I find confusing is that there's the key column listing out one of the indexes from the list of possible_keys(not shown in picture) however only the top row makes mention of Using index explicitely in the Extra column.

What does this mean in the 2nd row, is it not using the index listed
in the key column?
How should I interpret what the contents of the
key column is about and how it is used?
enter code here

a. Should I interpret this as
that index is used in the where stage of this query?


texts:
In the following statement (PostgreSQL 11):

=> SELECT c cost FROM tt;
ERROR:  syntax error at or near "cost"
LINE 1: SELECT c cost FROM tt;

I get an error. Adding parenthesis around the field expression doesn't help (SELECT (c) cost FROM tt;). But adding the AS keyword fixes it.

=> SELECT c AS cost FROM tt;
 cost
------
    1
...

I realize that cost is a keyword, but my impression is that the AS keyword is optional.

From a language perspective, why is the AS keyword required (or helpful) here? Is the PostgreSQL behavior here standard or documented somewhere?

Are there any other cases when the AS keyword is required?


texts: Mutual authentication via SSL Oracle 11g

texts:
I configure mutual authentication via SSL between client (Windows 7) and server (Windows Server 2008 R2). I create wallet via Oracle Wallet Manager (OWM) both on client and server, then  I create certificate requests for client and server in OWM, as it's shown in image below.
I create CA-certificate in OpenSSL according to:
openssl> genrsa -out root.key 2048
openssl> req -x509 -new -key root.key -days 1000 -out root.crt

Then I create sertificate request:
openssl> genrsa -out serv.key 2048
openssl> req -new –key serv.key –out serv.csr

Further I copy contents of cerfiticate request from OWM to *.csr file, that was created via OpenSSL, create sertificate from this file and sign it with CA certificate.
openssl> x509 -req –in serv.csr -CA root.crt -CAkey root.key -CAcreateserial -out serv.crt -days 500

Similary, I create a client certificate and sign it with CA certificate. Next, I import CA-certificate to OWM via "Import Trusted Certificate" both in client and server and a client or server sertificate via "Import User Certificate" on client or server-machine accordingly. Then save wallets and set "Auto Login" flag.
Next I create connection via Net Manager -> Local -> Profile -> Oracle Advanced Security -> SSL on both client and server. Listener.ora on server now contains this:

SSL_CLIENT_AUTHENTICATION = TRUE
WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY
= C:\OraWallet) ) )
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL =
TCP)(HOST = WIN-QV6N8G35RD5.localdomain)(PORT = 1521)) ) (DESCRIPTION
= (ADDRESS = (PROTOCOL = TCPS)(HOST = WIN-QV6N8G35RD5.localdomain)(PORT = 2484)) ) )

Then I add new "TCP/IP with SSL" connection on client and test it, but error appears on client:

ORA-28864: SSL connection closed gracefully

I made trace on server and there is 2 errors:

TNS-12560:TNS: protocol adapter error
TNS-00540:SSL protocol adapter failure

Well, now I don't know, how to fix them.


texts: How to quantify rollback events in SQL Server between 2 dates

texts:
I need to quantify rollback events and find theirs reasons, without change the application code. There is any way to collect this data?

The reason to check the rollback occurrences have need to identify whether the change of configuration in my jboss datasource from local-tx transaction system to no-tx will impact the batch executions


texts:
I am desperately looking for some logic or script to check commands in msrepl_commands where number of commands which have already been moved to subscriber but not deleted yet.

I am running the procedure manually:

EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 72

but it is not deleting any rows even though I checked msrepl_commands and found there are 20M rows that all came in between 2016-08-30 00:01:00 AM and 2016-08-31 09:00:00 AM,  but if I run distribution cleanup then it should delete top 10K in a loop till the row count reaches 2000.

I am a bit confused how to check those delivered commands (msdistribution_status gives me dilevered and undilevered commands but that is cumulative, and I need current status).


texts: How do you you suppress a specific warning in the postgresql log output?

texts:
Is there any way to suppress a specific warning in the postgresql log output?

Basically, I have an application that often does a spurious commit before beginning database operations, and it's leading to thousands of messages in the output of:

2018-12-02 01:37:22 PST [5147-152] <user>@<database> WARNING:  there is no transaction in progress

I realize this isn't an ideal situation, but everything seems to be working otherwise, but the logs are getting hugely cluttered with this specific warning.

How can I prevent postgresql from emitting this one, specific warning? I don't want to disable warnings entirely, because I do like to check for any other warnings.


texts: MongoDB 3.4 Sharding Error. “Cannot accept sharding commands if not started with --shardsvr”

texts:
I have installed mongoDB 3.4.5 in centOS 7.2 machine. I am configuring sharding in standalone mode.

Steps I followed:

For Replica Sets:

mongod --replSet rs0 --dbpath /data/srd/rs0 --logpath /data/srd/rs0/log.rs0 --port 27000 --fork --logappend --smallfiles --oplogSize 50 &
mongod --replSet rs0 --dbpath /data/srd/rs1 --logpath /data/srd/rs1/log.rs1 --port 27001 --fork --logappend --smallfiles --oplogSize 50 &
mongod --replSet rs0 --dbpath /data/srd/rs2 --logpath /data/srd/rs2/log.rs2 --port 27002 --fork --logappend --smallfiles --oplogSize 50 &

For Shards:

mongod --replSet rs0 --shardsvr --dbpath /data/srd/srd0 --logpath /data/srd/srd0/log.srd0 --port 27020 --fork --logappend --smallfiles --oplogSize 50 &
mongod --replSet rs0 --shardsvr --dbpath /data/srd/srd1 --logpath /data/srd/srd1/log.srd1 --port 27021 --fork --logappend --smallfiles --oplogSize 50 &
mongod --replSet rs0 --shardsvr --dbpath /data/srd/srd2 --logpath /data/srd/srd2/log.srd2 --port 27022 --fork --logappend --smallfiles --oplogSize 50 &

For Config server:

mongod --configsvr --replSet configReplSet --dbpath /data/srd/cfgrep0 --logpath /data/srd/cfgrep0/log.cfgrep0 --port 27006 --fork &
mongod --configsvr --replSet configReplSet --dbpath /data/srd/cfgrep1 --logpath /data/srd/cfgrep1/log.cfgrep1 --port 27007 --fork &
mongod --configsvr --replSet configReplSet --dbpath /data/srd/cfgrep2 --logpath /data/srd/cfgrep2/log.cfgrep2 --port 27008 --fork &

To start Mongos:

mongos --configdb configReplSet/sit:27006,sit:27007,sit:27008 --port 26060

I am able to login the mongos shell and also able to add the shards. But while enabling the shard I am getting exceptions as below.

mongos> sh.enableSharding("test");
{
        "code" : 193,
        "ok" : 0,
        "errmsg" : "Cannot accept sharding commands if not started with --shardsvr"
}


texts:
I am trying to move a mysql query to postgresql and I have a problem with the union operation, this is the query:

SELECT
          "user_firstname"
        , "user_lastname"
        , "course_fullname"
        , "course_completion_completeddate"
        , "course_completion_status"
        , "ESR Employee Number"
        , "DH Job Role"
        , "Department"
        from mdl_course_completions
        union
        SELECT
          auser.firstname AS user_firstname,
          auser.lastname AS user_lastname,
          course.fullname AS course_fullname,
          from_unixtime(base.timecompleted) AS course_completion_completeddate,
          CASE WHEN base.status = 50 OR base.status = 75 THEN 'Complete' END AS course_completion_status,
          COALESCE(user_9.data,'') AS user_custom_field_9,
          COALESCE(user_5.data,'') AS user_custom_field_5,
          COALESCE(user_49.data,'') AS user_department
        FROM mdl_course_completions base
         LEFT JOIN mdl_user auser
             ON auser.id = base.userid
         LEFT JOIN mdl_course course
             ON course.id = base.course
         LEFT JOIN mdl_user_info_data user_9
            ON user_9.userid = auser.id AND user_9.fieldid = 9
         LEFT JOIN mdl_user_info_data user_5
            ON user_5.userid = auser.id AND user_5.fieldid = 5
          LEFT JOIN mdl_user_info_data user_49
            ON user_49.userid = auser.id AND user_49.fieldid = 49
        WHERE
            (base.timecompleted > 1485953790
              and
            base.timecompleted < 1486040190
              and
            base.timecompleted IS NOT NULL)
              and
            course.id = 1730
              and
            base.status  IN (50,75)
        ;

The only function doesn't work is from_unixtime, but I changed for to_timestamp and it's ok, the problem is when I execute the query I get this error:

ERROR:  column "user_firstname" does not exist
LINE 2:           "user_firstname"

And I really don't understand why this runs with MySQL but does not with Postgres. Maybe I need to change it to join, so, any ideas?


texts: Performance of a Trigger vs Stored Procedure in MySQL

texts:
A post here on DBA.StackExchange (What are the best practices for triggers to maintain a revision number on records?) has spawned an interesting question (at least, interesting to me) regarding performance in MySQL.

The context is that we want to insert a record in a table for each row that is updated.  Before the row is updated, we want to store off a previous value and then increment one of the columns (a "version" column).

If we do this inside a trigger, it works out nicely.  For MySQL, the triggers are row by row, so it would be an easy solution.  Select the data currently in the table, insert it into the logging table, and the update the "version" column in the new data.

However, it's possible to move this logic to a stored procedure. If you do that, you're performing the insert, then incrementing the "version" column in the table.  The entire thing would be set based.

So, when it comes to performing this insert, would this be more performant to use the set-based stored procedure approach or a trigger based approach?

This question is for MySQL (since it has row-by-row triggers), although it could apply to other row-by-row trigger DBMSs.


texts: Strange behaviour in TSQL function (parameter with int variable or NULL behaves differently)?

texts:
I experienced very strange problem today with TSQL function.

The function has several parameters (int and bit). One of the (int) is set with value NULL.

When calling a function with NULL inside the params function return ERROR (the error is ok, because I try to convert '%%' to int value). When the function is called with param @p1 which value is NULL the function is working.

Can someone explain to me why it works like that?

We are using:

Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
    Oct 19 2012 13:38:57
    Copyright (c) Microsoft Corporation
    Business Intelligence Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

MY CODE IS:
The code that causes error:

((@p IS NOT NULL) AND (CONS.IDp = @p)) OR
((@p IS NULL) AND (UFCFD.IDUser IS NOT NULL) AND
 (CONS.IDp = CAST(UFCFD.Value1 AS INT))

REMARK: I edit "causing error" part because my colleague put a protection check. I remove that part to show the original state of the function (The source was: (CONS.IDp = CASE WHEN ISNUMERIC(UFCFD.Value1) = 1 THEN CAST(UFCFD.Value1 AS INT) ELSE NULL END))

The call that causes error:

SELECT * FROM dbo.myFunc(NULL);

The call that works:

DECLARE @p INT = NULL;
SELECT * FROM dbo.myFunc(@p);

P.S. If the question is unclear please let me know via comment.

EDIT2:

This is the full text of the function:

CREATE FUNCTION dbo.myFunc
(
    @IDUser INT,
    @p INT,
    @IDGrid INT,
    @IsWithLead BIT,
    @IDLeadValueResource INT,
    @IDLanguage INT,
    @IsAllPresent BIT,
    @IDAllValueResource INT
)
RETURNS TABLE
AS
RETURN
(
    SELECT
        -99999999 AS ID,
        dbo.funs_GetResourceText(@IDLanguage,@IDAllValueResource) AS [Text],
        -99999999 AS Sort
    WHERE (@IsAllPresent = 1)

    UNION ALL

    SELECT
        0 AS ID,
        dbo.funs_GetResourceText(@IDLanguage,@IDLeadValueResource) AS [Text],
        0 AS Sort
    WHERE (@IsWithLead = 1)

    UNION ALL

    SELECT
        CONS.IDConsumerType AS ID,
        CASE
            WHEN @IDLanguage = 40001 THEN CONS.ConsumerType_Name_en
            WHEN @IDLanguage = 40002 THEN CONS.ConsumerType_Name_it
            WHEN @IDLanguage = 40003 THEN CONS.ConsumerType_Name_de
            WHEN @IDLanguage = 40004 THEN CONS.ConsumerType_Name_fr
            ELSE CONS.ConsumerType_Name_en END AS [Text],
        ROW_NUMBER() OVER(ORDER BY CONS.SegmentOrder ASC) AS Sort
    FROM
        dbo.V_ConsumerTypes AS CONS
        LEFT JOIN dbo.Grids GRD ON (GRD.ID = @IDGrid)
        LEFT JOIN dbo.CONFieldCollection_FieldDefinition FCFD ON
            (FCFD.IDFieldCollection = GRD.IDGridFieldCollection) AND
            (FCFD.FieldName LIKE '%IDp%')
        LEFT JOIN dbo.CONUser_FieldCollection_FieldDefinition UFCFD ON
            (UFCFD.IDFieldCollection = GRD.IDGridFieldCollection) AND
            (UFCFD.IDUser = @IDUser) AND
            (UFCFD.IDFieldCollections_FieldDefinitions = FCFD.ID)
    WHERE
        ((@p IS NOT NULL) AND (CONS.IDp = @p)) OR
        ((@p IS NULL) AND (UFCFD.IDUser IS NOT NULL) AND
         (CONS.IDp = CAST(UFCFD.Value1 AS INT))
)

The call that works is:

DECLARE @IDUser INT = -100;
DECLARE @p INT = NULL;
DECLARE @IDGrid INT = 17;
DECLARE @IsWithLead BIT = 0;
DECLARE @IDLeadValueResource INT = 0;
DECLARE @IDLanguage INT = 40002;
DECLARE @IsAllPresent BIT = 1
DECLARE @IDAllValueResource INT = -177;

DECLARE @dt DATETIME = GetDate();
SELECT * FROM dbo.fun_ClROME_MdFillComboConsumerTypes(@IDUser, @p, @IDGrid, @IsWithLead, @IDLeadValueResource,
    @IDLanguage, @IsAllPresent, @IDAllValueResource)
ORDER BY Sort

If we use NULL instead of @p then I receive error, because I try to convert '%%' to int - this is correct.

The problem is that obviously function behaves differenly which is a big issue.


texts: In-line TVF is using indexed views in a UNION - parameter should eliminate most of them, except it doesn't

texts:
I am using SQL Server 2016-SP1-CU8

Here is the code to setup my scenario:

    --1. Drop indexed views (in case of re-running
    IF OBJECT_ID('Widgets_1', 'V') IS NOT NULL DROP VIEW Widgets_1
    IF OBJECT_ID('Widgets_2', 'V') IS NOT NULL DROP VIEW Widgets_2
    IF OBJECT_ID('Widgets_3', 'V') IS NOT NULL DROP VIEW Widgets_3
    GO

    --2. Setup table and data
    IF OBJECT_ID('Widgets', 'U') IS NOT NULL DROP TABLE Widgets
    CREATE TABLE Widgets (
        ID INT IDENTITY (1,1),
        WidgetName VARCHAR(128),
        WidgetTypeID TINYINT
    )

    INSERT dbo.Widgets
    (
        WidgetName,
        WidgetTypeID
    )
    SELECT TOP (100000)
        a.[name],
        a.[object_id] % 3
    FROM sys.columns a
    CROSS JOIN sys.columns b

    -- See what we have populated
    SELECT WidgetTypeID, COUNT(1) cnt
    FROM dbo.Widgets
    GROUP BY WidgetTypeID
    GO

    --3. Create indexed views
    IF OBJECT_ID('Widgets_1', 'V') IS NOT NULL DROP VIEW Widgets_1
    GO
    CREATE VIEW Widgets_1
    WITH SCHEMABINDING
    AS
    SELECT ID,
           WidgetName
    FROM dbo.Widgets
    WHERE WidgetTypeID = 1
    GO
    CREATE UNIQUE CLUSTERED INDEX UCDX_ID ON Widgets_1 (ID)
    GO

    IF OBJECT_ID('Widgets_2', 'V') IS NOT NULL DROP VIEW Widgets_2
    GO
    CREATE VIEW Widgets_2
    WITH SCHEMABINDING
    AS
    SELECT ID,
           WidgetName
    FROM dbo.Widgets
    WHERE WidgetTypeID = 2
    GO
    CREATE UNIQUE CLUSTERED INDEX UCDX_ID ON Widgets_2 (ID)
    GO

    IF OBJECT_ID('Widgets_3', 'V') IS NOT NULL DROP VIEW Widgets_3
    GO
    CREATE VIEW Widgets_3
    WITH SCHEMABINDING
    AS
    SELECT ID,
           WidgetName
    FROM dbo.Widgets
    WHERE WidgetTypeID = 3
    GO
    CREATE UNIQUE CLUSTERED INDEX UCDX_ID ON Widgets_3 (ID)
    GO

    --4. Create function using indexed views
    IF OBJECT_ID('udf_Get_Widgets', 'IF') IS NOT NULL DROP FUNCTION udf_Get_Widgets
    GO
    CREATE FUNCTION dbo.udf_Get_Widgets
    (
        @WidgetTypeID TINYINT
    )
    RETURNS TABLE
    AS
    RETURN
        SELECT
            ID,
            WidgetName
        FROM dbo.Widgets_1 WITH (NOEXPAND)
        WHERE 1 = @WidgetTypeID
        UNION ALL
        SELECT
            ID,
            WidgetName
        FROM dbo.Widgets_2 WITH (NOEXPAND)
        WHERE 2 = @WidgetTypeID
        UNION ALL
        SELECT
            ID,
            WidgetName
        FROM dbo.Widgets_3 WITH (NOEXPAND)
        WHERE 3 = @WidgetTypeID
    GO

And here is my question. If I query the function using hard-coded parameter value like so:

    SELECT  ID,
            WidgetName
    FROM    dbo.udf_Get_Widgets (2)

I can see that only one of the underlying indexed views is utilized - this is what I would expect:

However, if I now run a query with a parameter:

    DECLARE @WidgetTypeID TINYINT = 2
    SELECT  ID,
            WidgetName
    FROM    dbo.udf_Get_Widgets (@WidgetTypeID)

I can see that all of the indexed views are accessed, even though two of them are unnecessary:

In real-life scenario, these could be very large tables with millions of rows and I am concerned that this will kill my performance in production. Should I be worried? BTW, all of this acrobatics is done as part of performance tuning exercise for EF queries, so I am prevented from using many of the normal performance tuning techniques.


texts:
I tried to install Oracle database 12c on my machine following the documentation:

http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/Windows_DB_Install_OBE/Installing_Oracle_Db12c_Windows.html

I have successfully installed the database itself and can login to the database pdborcl using:

sqlplus sys/Oracle_1@pdborcl as sysdba

I unlocked the hr user following the instruction using:

alter user hr identified by hr account unlock;

However, when I tried to connect to the database using the newly unlocked user: hr, it gives an error message:

SQL> connect hr/hr@pdborcl
ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege.

Warning: You are no longer connected to ORACLE.

Can someone let me know how to solve this problem? Is this something with licensing?

UPDATE:

I actually tried:

alter system disable restricted session;

What I get is:

ERROR at line 1:
ORA-65144: ALTER SYSTEM DISABLE RESTRICTED SESSION is not permitted.

The query: select logins from v$instance returns:

LOGINS
---------
ALLOWED


texts: SQL view, how to append results from tableB with the results from tableA

texts:
I am using SQL server 2012. I would like to generate a sql view where I got all records from tableA. And below these results I would like to have the results from tableB. There are no relations between these 2 tables. The columns are also differant. Here is an example:

tableA:                       tableB:

+-------------------------+   +--------------------------------+
| Name                        | Year
---------------------------   +---------------------------------
| A    |                      | 2000    |
| B    |                      | 2001    |
| C    |                      | 2002    |

I would like to have results like this:

 +-------------------------+
    | Name | Year
    ------------------------
    | A    |
    | C    |
    | D    |
    |      | 2000
    |      | 2001
    |      | 2002

How can I realize this? I see only joins, union solutions, but they dont work for me because I dont have some relation between these 2 tables.


texts: MySQL 5.1: change datadir variable value in my.cnf

texts:
Below is my system configuration:

OS: RHEL5
MySQL: 5.1.73
Default datadir=/var/lib/mysql/

But, the above directory has space crunch. So, I decided to change the path. I found /u000 directory has enough space. So, I created /lib/mysql/ under /u000 and assign permission (chmod) as 0777.
Then after, I changed the below values in mysql configuration file: /etc/my.cnf:

 innodb_data_home_dir = /u000/lib/mysql
 innodb_data_file_path = ibdata1:10M:autoextend
 innodb_log_group_home_dir = /u000/lib/mysql
 log_error = /u000/lib/mysql/rpc4939.err
 general_log_file = /u000/lib/mysql/rpc4939.log
 slow_query_log_file = /u000/lib/mysql/rpc4939-slow.log
 datadir = /u000/lib/mysql/
 pid_file = /u000/lib/mysql/rpc4939.pid

Then, tried to restart mysql service but it failed.
When, I changed the datadir as  datadir = /var/lib/mysql/ it started working.

Can I know Why am I unable to change the datadir?
Is there any special way to change the datadir?
log_error value is always pointing to /var/lib/mysql/ instead /u000/lib/mysql/. How this variable can be changed?


texts: MySQL: replicating to a different table engine type

texts:
According to the replication documentation from MySQL, it is possible to set up replication from InnoDB source tables to MyISAM destination tables. Unfortunately, the documentation has little to say about drawbacks, data consistency implications (apart from the CASCADE corner case) and recommended settings for enabling such a replication configuration. So just a number of questions come to my mind regarding this setup:

is statement-based replication preferred over  the row-based variant or vice-versa?
does either the InnoDB or the MyISAM side need some specific settings so data consistency would not break all by itself as the result of "normal" replication activity?
are DDL executions on the master handled on the slave in a sane manner?
how would I prevent an ALTER TABLE blah ENGINE=InnoDB statement run at the master from propagating to the slave?
Is there a recommended way of setting up such a "split" setup? I could think of a direct ALTER TABLE blah ENGINE=MyISAM on the slave, is this viable?
any other caveats one should know about?


texts: Internal Online Store Database Design Issue

texts: I am trying to create a database for an internal store that we are developing to manage our internal items (like phones, computers, office equipment, etc). The issue Ive run into is that they want each item to be able to support add-ons. The only issue I have with this is they also want the prices to be dynamic based on your region for both items and addons. So a different region may have a different price than some other region. The Items table right now supports this through a foreign key to the Availabilities table. What is the best practice to get the Addon's table to be a foreign key on the Availabilities table so that it to can have multiple prices per region? Is my design totally messed up here?


texts: Duplicate Relationships in Rails/MySQL Healthcare Application

texts:
Overview of the Application

I am building a Ruby on Rails application to organize medications, using MySQL. The goal of this application is to provide dosage specific treatment recommendations, and also provide more general information about the utility of drugs to treat various conditions.

Current Entities

“Drug”  - Example: “Abilify”

“Dosage” - Example: “10mg”

“Condition" - Example: “Depression”

Potential Entities For Required Use Cases

“Prescription” (Drug + Dosage + Condition) => This is required for dosage specific data, for a specific condition.  Clinical research may be attached to this entity.

“Therapy" (Drug + Dosage) => This is required because it represents how most medications are priced, and how most medications are interchanged between 3rd party health applications, using a government standards primary key known as a RxNorm Concept Unique Identifier.

“Usage” (Drug + Condition) => This is required for connecting drugs to conditions, without consideration to dosage. Some drugs are used to treat many conditions.

Concern

These three models, if implemented, create duplicate relationships between either drug and condition, or drug and dosage.  How can we build out a normalized system that supports our needs for both dosage and condition specific tools, as well as dosage and condition agnostic tools?

Example

“Abilify 5 mg for depression“ => The “Prescription” entity would allow us to attach clinical studies, and alternatives, for 5 mg of Abilify in the treatment of depression.  The “Therapy” entity would allow us to find pricing, as well as 3rd party supplemental data, for 5 mg of Abilify.  The “Usage” entity would allow us to track all conditions that Abilify is used to treat, along with their efficacy in treating these various conditions.


texts: Is it possible to change ER diagram style in MySQL Workbench? How?

texts:
I am really new to database visualization. I am creating the ER diagram of my databases. I am using MySQL Workbench 6.3 (community version). I am creating the diagram using the default style. The style looks like this:

Is it possible to change the style to be like the image below?

If yes, How can i do this?
or if you have another database designer that can create diagram like the second style, it would help.
Thank you!


texts: Comparing two queries in SQL Server 2012

texts:
I am comparing two queries in SQL Server 2012. The goal is to use all of the pertinent information available from the query optimizer when choosing the best query. Both queries produce the same results; the maximum orderid for all customers.

Clearing out the buffer pool was done before executing each query with FREEPROCCACHE and DROPCLEANBUFFERS

Using the information provided below, which query is the better choice?

-- Query 1 - return the maximum order id for a customer
SELECT orderid, custid
FROM Sales.Orders AS O1
WHERE orderid = (SELECT MAX(O2.orderid)
                 FROM Sales.Orders AS O2
                 WHERE O2.custid = O1.custid);

-- Query 2 - return the maximum order id for a customer
SELECT MAX(orderid), custid
FROM Sales.Orders AS O1
group by custid
order by custid

STATISTICS TIME

Query 1 STATISTICS TIME: CPU time = 0ms,  elapsed time = 24 ms

Query 2 STATISTICS TIME: CPU time = 0 ms,  elapsed time = 23 ms

STATISTICS IO

Query 1 STATISTICS IO: Table 'Orders'. Scan count 1, logical reads 5, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Query 2 STATISTICS IO: Table 'Orders'. Scan count 1, logical reads 4, physical reads 1, read-ahead reads 8, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Execution Plans

SELECT properties Query 1

SELECT properties Query 2

Conclusions:

Query 1

Batch cost 48%
Logical Reads 5
Physical Reads 2
Read-ahead Reads: 0
CPU Time: 0ms
Elapsed Time 24ms
Estimated subtree cost: 0.0050276
CompileCPU: 2
CompileMemory: 384
CompileTime: 2

Query 2

Batch cost 52%
Logical Reads 4
Physcial Reads 1
Read-ahead Reads: 8
CPU Time 0
Elapsed Time 23ms
Estimated subtree cost: 0.0054782
CompileCPU: 0
CompileMemory: 192
CompileTime: 0

Personally, even though Query 2 has a higher batch cost according to the graphical plan, I think its more efficent that Query 1. This because query 2 requires less logical reads, has a slightly lower elapsed time, the compilecpu, compilememory and compiletime values are lower. read-ahead reads are 8 for query 2 and 0 for query 1.

Update 12:03

Clustered Index definition

ALTER TABLE [Sales].[Orders] ADD  CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
    [orderid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Non-Clustered Index idx_nc_custid

CREATE NONCLUSTERED INDEX [idx_nc_custid] ON [Sales].[Orders]
(
    [custid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


texts: Change character limit of SQL Agent Operators

texts:
I want to add more than 100 characters worth of email addresses to the SQL Agent Operators alerting.

For example: emaione@example.com;emailtwo@example.com;emailthree@example.com;....

I tried to get around the 100 character limit by altering sysoperators email_address column as such

ALTER TABLE sysoperators
ALTER column email_address NVARCHAR(1000);

and then creating my operator, but the addresses are still cut off at 100 characters?


texts:
We see Direct path read temp frequently in the database. It started with one query and later followed up to all the queries running in database at that moment. I understand that data is read from temporary tablespaces and this event is usually caused by a sort operation that cannot be complete in memory and requires storage access. First response is to tune the query, but the same query runs better previous day or next day.

My question, is there a way to check how much memory is used by a query or process at that moment? Or anything related to finding out the memory usage would be great. I appreciate your help in advance. Queries are running parallel and it is dataware house environment.


texts: Drop table without its dependencies and solve block corruption

texts:
I have found alert about corrupted block in alert log. Corrupted block belonged to the table named DOCS. Because of I don't have any backup of this database, decided to recreate DOCS table to solve the corruption. And here is a problem.

Generated all scripts:

1. DDL for table.
2. DDL for constraints,triggers,indexes on the table.
3. DDL for the constraints that references this table.
4. Saved grants on this table.

Now I want to drop table , but left its dependencies , and then recreate table with its constraints,triggers,indexes..

How can I do that?

------------------------Edit

It seems to be very simple.

Save data of this table

  create table new_table as select * from my_table;
  Save DDL for table and its comments
  DDL for indexes on this table
  DDL for constraints on this table
  DDL for Foreign key-s that references on this table
  Save grants on this table
  Drop table purge
  Recreate table, comments, constraints, indexes, foreign keys that references to this table
  Give grants
  insert data

  insert into my_table as select * from new_table

This solution works for re-creation the table(it is a correct way to recreate a table). But the corruption did not solved, during the insertion into the table in Alert.log raised the same error about block corruption.

In comments @Mindaugas Riauba wrote a solution and it worked. I just want to know why my solution about re-creation table did not work and @Mindaugas Riauba's solution work?

Thank you @Mindaugas Riauba again!


texts: Set values conditionally in Postgres UPSERT

texts:
We have an UPSERT feature achieved through on conflict do set. How can I set the values conditionally in the set statement, like

if(excluded.col1 is null) col1=table.col1 else col1=excluded.col1

We are using Postgres 9.5.


texts:
I have a big InnoDB table (7 million records) and I need to speed up the insert/update operations.
For instance, this is what the table looks like:

CREATE TABLE product (
    year int not null,
    barcode varchar(8) not null,
    primary key(year, barcode)
) ENGINE=InnoDB;

I thought about partitioning the table by year:

CREATE TABLE product_2016 (
    year int not null,
    barcode varchar(8) not null,
    primary key(year, barcode)
) ENGINE=InnoDB;

CREATE TABLE product_2017 (
    year int not null,
    barcode varchar(8) not null,
    primary key(year, barcode)
) ENGINE=InnoDB;

I need to read from the union of all the partitioned tables, so I need to merge them in some way, like you can do with the MERGE engine:

CREATE TABLE product (
    year int not null,
    barcode varchar(8) not null
) ENGINE=MERGE UNION=(product_2016, product_2017) INSERT_METHOD=LAST;

Of course I cannot do it in this way because the MERGE engine only works with MYISAM tables.

Is the VIEW an alternative? I don't want to slow down the read operation (the read time should be the same). Are there other alternatives?


texts: Can a trigger create a TABLE when inserting data?

texts:
I have a trigger that keeps track of history of table dev by inserting a row in table dev_hist:

CREATE OR REPLACE FUNCTION process_dev_hist() RETURNS TRIGGER AS $dev_hist$
    DECLARE action_name text;
    BEGIN
        IF TG_OP = 'DELETE' THEN
            OLD.timestamp := current_timestamp;
            action_name := 'deleted';
            INSERT INTO dev_hist SELECT action_name, user, OLD.*;
        ELSE
            NEW.timestamp := current_timestamp;
            IF (TG_OP = 'INSERT') THEN
                action_name := 'added';
            ELSIF (TG_OP = 'UPDATE') THEN
                action_name := 'modified';
            END IF;
            INSERT INTO dev_hist SELECT action_name, user, NEW.*;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$dev_hist$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS dev_hist ON dev;
CREATE TRIGGER dev_hist AFTER INSERT OR UPDATE OR DELETE ON dev
    FOR EACH ROW EXECUTE PROCEDURE process_dev_hist();

Problem is, if table dev_hist doesn't exist, it throws error.

Is it possible to change it, so it would create table if it doesn't exist?


texts: Missing CALLs on identifiers_all using PL/SCOPE

texts:
Posted this on StackOverflow, but maybe this is the right place for it:
I'm trying to use the view all_identifiers from PL/SCOPE to find out calls to procedures and functions but it's not showing all the calls, some are missing.

I have the following code in a package (PKG_1) at line 4216:

tmpParameterId:=  PKG_2.function1(var1, var2, var3);

And when I run the query:

select * from ALL_IDENTIFIERS where  upper(object_name) = 'PKG_1' and line = '4216';

I get the following results(owner|name|type|object_name|object_type|usage|line):

SYSADM  var3 FORMAL IN  PKG_1 PACKAGE BODY  REFERENCE   4216
SYSADM  var2 FORMAL IN  PKG_1 PACKAGE BODY  REFERENCE   4216
SYSADM  var1 FORMAL IN  PKG_1 PACKAGE BODY  REFERENCE   4216
SYSADM  TMPPARAMETERID  VARIABLE    PKG_1 PACKAGE BODY  ASSIGNMENT  4216

And I do not get the CALL usage to function1. I have similar code in other parts of the package and it shows those calls. Really lost here as I cannot tell why some CALLs are being stored in all_identifiers and some are not.


texts:
I have Created a trigger, where I am storing some specific data in a table and after the trigger gets executed I am deleting that Table.

The query :

CREATE TRIGGER `afterinsert_Golden_Ball` AFTER INSERT ON `team_trophies` FOR EACH ROW

BEGIN

    DECLARE badgeID INT(10);

   CREATE TABLE tempname AS
     (SELECT AVG(d.fair_play) as AvgPlay , b.tournament_id , d.* FROM
     `team_trophies` a INNER JOIN tournament_scheduling b ON
      a.tournament_id = b.tournament_id INNER JOIN matches c ON b.id
      = .match_id INNER JOIN user_match_result d ON c.id = d.match_id WHERE
      a.tournament_id = 1 GROUP BY d.user_id);

  IF EXISTS (SELECT * from tempname WHERE AvgPlay = (SELECT MAX(AvgPlay) FROM tempname))

  THEN

        SELECT badge_id INTO badgeId FROM badge_master WHERE name  = 'Golden Ball';

        Insert into user_badges(badges_id , user_id , match_id , turf_id ,
           tournament_id , created_date)
        SELECT  badgeID , user_id , match_id , NULL , tournament_id ,
           created_date from tempname limit 1;

   END IF;

   DROP TABLE tempname;

END

MySQL Error : 1422 Explicit and Implicit Commit is not allowed in SP and Trigger

Kindly Help.


texts:
Similar to my previous question : Prevent Non Copy Only Backups, how do I create a user that can do everything to my database <EXCEPT> take backups at all?

I've granted them every role except DB_backupOperator and DB_Owner. The issue is that the user will need to create objects (stored procedures, functions, tables, whatever) in the database.

With these roles, that is possible, however, they are not able to use the objects that they create.

I.E.

create proc test as select 1
go
grant exec on test to [OtherUser]

this completes successfully

grant exec on test to [TheCurrentUser]

throws an error

  Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself

Also, if I just try to execute the stored procedure, I get an

  EXECUTE permission denied

I'm open for ideas.

Thanks.


texts: What is the proper way of Adding a Field in a MySQL table with 1.6 million row

texts:
I have a table with 1.6 million rows.

Then I add a field on that table.

I am using hostgator as hosting. You know, they have like 16 core CPU.

Result:
1. phpmyadmin doesn't work for 10 minutes
2. After database back online the field I want to add is NOT added.
3. Fortunately I do not get a call from hostgator complaining.

I don't care it's slow. I know it's slow. It's 1.6 million row. I just want to do this once.

Is this because I use innodb engine?

The command is simple:

Alter table tablebusiness add column `Prominent` int(10) NOT NULL

I got this error:

#2013 - Lost connection to MySQL server during query

I do not need this operation to be run fast

I need it to run once.

I suppose I can copy the table somewhere else and then switch the old table into new table but innodb has cascade and I do not know how to do so.

Other update: Hostgator is pissed. Can't blame them. I must have used huge CPU power.

Looks to me there is a proper way to modify a table. Maybe shutting down the database first in maintenance mode or something. Maybe creating a copy of table first.


texts:
We are creating a system that has a button for converting MS Access DB to MySQL. Is there a way to convert MS Access DB to MySQL using PHP?

I would really appreciate any inputs on this one. Thank you in advance folks!


texts: Difference between avg Disk sec/write and io_stall_write_ms

texts:
We have a SQL Server 2016 SP2 Enterprise with latest CU with the database files spread out over different disks.
So we have data,log, tempdb and system db's have each their own drive. Date and log only contain one file.Those drives each has their own LUN on a All-Flash SAN.
To monitor the latency I capture sys.dm_io_virtual_file_stats every 15 minutes and then calculate the latency using the previous snapshot.
For the write latency I'm using following calculation:
(io_stall_write_ms - lag (io_stall_write_ms,1,0) over (order by checkdate))/(num_of_writes - lag (num_of_writes,1,0) over (order by checkdate)) write_latency

I'm getting an average write latency of 10ms but when I'm starting perfmon (with duration set to 900 seconds) and monitor the avg. Disk sec/Write in the same period for the same drive I'm only getting an average write latency of 3 ms.
I'm also capturing the wait stats for the same period, when I look at PAGEIOLATCH_EX waits and calculates how long every wait took, I'm also getting a value of approx 3ms.
I thought io_stall_write_ms represented the same as avg. Disk sec/Write or am I missing something?
Can somebody explain this behavior?


texts: in MySQL what is the query to show these student's that each marks less than 50?

texts:
I have this table marks in MySQL database

I used a query to output this table that shown as below:

it's my output:

I want use a query to show these student's that each marks less than 50,
output for example above only Liza and Jake with marks appear

What is the MySQL query for do this work? how i can?


texts:
I'm trying to login into fresh install mysql with:

sudo /usr/local/mysql/bin/mysql -u root

But getting following error:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

The mysql server started with:

sudo /usr/local/mysql/bin/mysqld --user=root

What is the default login/password for mysql root user?

Thank you.


texts: How to know what are the necessary log files for a backup in DB2?

texts:
Is there a way to know what are the necessary log files for specific backup?

Un example of backup and restore process:

db2 BACKUP DATABASE TST1 ONLINE TO '/db2/backup'
db2 RESTORE DATABASE TST1 FROM '/db2/backup' TAKEN AT 20150826131633 TO '/db2/data' INTO TST2

db2 rollforward db TST2 to end of logs
db2 rollforward db TST2 complete

Now, in order to be able to rollfoward to end of logs I need to copy the logs from the backuped DB to the target archive dir. What I do is coping the last 2 days and it always passed. Some day it will not.

So how can I know during backup time(right after it) what files exactly in addition I need to backup..?


texts:
I have the following tables in my database:

Above these tables (not pictured) is a Users table where the SendingUserId and ReceivingUserId are keyed onto (they are foreign keys).

Are these tables in a normalized form? I feel as if I'm missing something.. maybe the SendingUserId and ReceivingUserId should be a composite key in the UsersWithMessages table?


texts: Troubleshooting high network traffic between primary and secondary replicas

texts:
We have two SQL server hosts. There are about 12 availability groups, where the primary replica is on the "primary" host and the secondary replicas are on the "secondary" host. Now, for one reason or another, every Saturday, the network out of the primary and the network in of the secondary spike up about 600% compared to other days. It stays this way all day. It abruptly starts in the morning and abruptly stops at night.

Given the fact that the network in of the primary does not spike, we are assuming that the network traffic is somehow involved with an "internal" process between the two hosts.

This is obviously to general of a situation to just toss a solution at me. My question is, what data or troubleshooting techniques would be helpful with finding the reason behind this traffic?

I'm familiar with the SQL profiler and activity monitor, but I don't feel like these will be helpful if the network traffic is not the result of clients' queries. I can go as low-level as wireshark, but I don't think I'll be able to discern what the traffic is related to from the packets.

Some things we've already ruled out:

Heavy client query load on Saturdays
Scheduled jobs on Saturdays