texts: MySQL databases on their own separate Logical Volume

texts:
I'm looking for a way to keep MySQL database sizes within certain limits. That's why I was thinking about creating a logical volume for each database. So for example:

db1 => lv_db1 (5 GB)
db2 => lv_db2 (5 GB)
...

This way the databases will not be able to grow beyond their 5 GB.

I know the databases are stored in /var/lib/mysql/<database>, so my idea is to mount the logical volume to this folder. However, the problem now is that I need to create and mount the logical volume before creating the database, which causes the CREATE DATABASE to fail because it thinks the database already exists.

ERROR 1007 (HY000): Can't create database 'foo'; database exists

Does anyone have suggestions how I can achieve this?

My backup solution is to mount one logical volume to /var/lib/mysql, but that way one database could claim all the space.


texts: Error "In a grant or revoke statement, the user attempted to grant a tablespace quota above the upper limit." when trying to increase user quota

texts:
I'm attempting to increase the space quota on a particular schema. I'm using the following query to do so:

ALTER USER MY_SCHEMA QUOTA "100 M" ON "USERS";

This results in the following error:

SQL Error: ORA-02187: invalid quota specification
02187. 00000 -  "invalid quota specification"
*Cause:    In a grant or revoke statement, the user attempted to grant
           a tablespace quota above the upper limit.
*Action:   Grant a smaller tablespace quota.

But this doesn't make any sense. The user already has a 50 MB quota, and a query trying to set it to 25 MB fails with the same error message:

ALTER USER MY_SCHEMA QUOTA "25 M" ON "USERS";

I've confirmed that my tablespace has enough room left. (Its data files can acquire about 2 GB more of disk space.) What is causing this error?


texts: Importing different file types to SQL Server

texts:
I'm looking for direction here.  I need to automate a process that will import several different file types into SQL Server.  They are all text based files (no excel) but the internal format of the files is different.  Some are comma delimited, others are tab delimited.  They all contain the same base information (customer name, address, phone, etc.) but some files contain additional data.  I want to only import the common data from each file.  So, some data will be skipped over in one file but in another file all the data needs to be imported.

I was going to use an SSIS ForEach loop but I'm having trouble figuring out how to handle multiple file formats.  I get stuck on the connection string for setting up how to import the files.  Has anyone come across this before?  If so, can you push me in the right direction?


texts: Using wildcards in a like statement on an unindexed VARCHAR(MAX) column with more than 1 million records

texts:
In order to troubleshoot a problem, I have a one-time question as to whether a specific varchar(max) field contains non-printing ASCII characters (other than white-space). The following is my straightforward idea about how to determine if there are such characters stored in our production database.

SELECT TOP 10 [CaseNoteId]
      ,[CaseId]
      ,[CaseNote]
  FROM [DB].[XY].[ReferralCaseNotes]
  WHERE CaseNote LIKE ('%[' + CHAR(1) + '-' + CHAR(8) + CHAR(11) + CHAR(12) + CHAR(14) + '-' + CHAR(31) + CHAR(127) + ']%')

My hesitancy to actually run this stems from using wildcards in the LIKE pattern, that there are over a million records in the table in question, the lack of a full-text index on this column, and that this will likely be an exhaustive search because we do not believe that any such characters exist.

I am a neophyte. How can I estimate whether running this query will be a significant load on our production system? Also, is there a better way to get at the same information?

Possible Improvements:

I'm not worried about data changing while my query runs. Can I change this query to look at a few rows at a time in a way that is beneficial?
Can I set this query to somehow be a background operation that doesn't get in the way of any other queries?
Can I run it for a limited time and determine what percentage of the table was searched, so that I can estimate the time required for a full search?
Would WITH(READPAST) improve my performance?

Why?

The database in question involves sensitive data, the government, and security folks making rules. Restoring a backup to a different server makes a ton of sense, but would cost the taxpayer several orders of magnitude more than makes any sense.

If the answer is, "Don't worry, you're just doing a SELECT," then I say, "Great!"


texts: MySQL -> Loop through a table, running a stored procedure on each entry

texts:
I have a database with 'books' (short stories for children) and it would be extremely informative to have word counts of each word in the books.

I figured out how to get the word count for each word using:

SELECT SUM
(
    ROUND
    (
        (LENGTH(pageText) - LENGTH (REPLACE (pageText, "Word", "")))
        /LENGTH("Word")
    )
) FROM pages WHERE bookID = id;

Which works wonderfully for counting the words. BUT it requires me to go through each book, and get each word out, and run it through that function (I have it saved as a Stored Procedure.)

I have a table that contains each word, with no duplicates.

My question: is there a way I can do some kind of "for each" loop on the Words table using my stored procedure?

ie. pass the stored procedure a book ID and a word and record the result. Doing EVERY word, for EVERY book. Thus saving me a LOT of manual time... Is this something I should even be doing from the DB side? Should I attempt it with PHP instead?

Honestly any input is greatly appreciated!


texts:
Permissions required to create a Spatial Index on a table, for this BOL says:

  Requires ALTER permission on the table or view. User must be a member
  of the sysadmin fixed server role or the db_ddladmin and db_owner
  fixed database roles.

However, I find that the Alter permission alone is sufficient to create a Spatial Index on a table. (without having server Sysadmin rights)

The test I have created below: (run each batch to find out what is happening)

USE adventureworks;
GO
/* Start these scripts with a priveledged account */

CREATE TABLE employees
(employeedid INT PRIMARY KEY IDENTITY(1,1),
empaddress GEOGRAPHY
)
INSERT INTO employees VALUES(geography::STGeomFromText('LINESTRING(47.656 -12.360, 47.656 -12.343)', 4326))
GO

/* Create our test SQL account with limited access = no securables yet */
CREATE LOGIN LimitedPerms WITH Password = 'pass';
CREATE USER LimitedPerms FOR LOGIN LimitedPerms;
GO

EXEC AS USER='LimitedPerms';
SELECT SUSER_NAME() AS 'CurrentlyLoggedInUser'
GO

/* Should give an error = does not have any rights yet */
CREATE SPATIAL INDEX emp_spatIx on employees(empaddress);
GO

REVERT;
SELECT SUSER_NAME() AS 'CurrentlyLoggedInUser';
GO

/* Give alter permission on table: employees to our test user */
GRANT ALTER ON OBJECT::employees to LimitedPerms;
GO

EXEC AS USER='LimitedPerms';
SELECT SUSER_NAME() AS 'CurrentlyLoggedInUser';
GO

/* Allows user to create index without being a sysadmin! */
CREATE SPATIAL INDEX emp_spatIx on employees(empaddress);
GO

REVERT;
GO

Anyone care to test and report back? or inform why documented like this?

Thanks

EDIT1: This was tested on SQL Server 2008 Enterprise Edition

EDIT2: I have put feedback on Microsoft Connect


texts:
I have a big database with 50 tables and each table can contain between 100,000 to 100 million records. On these tables are there are 1000s of indexes, but many of the indexes are now not in use.
How to find those indexes and remove them?
I am a beginner with MariaDB and MySQL, so with the answer, if you could mention resources to study this, that would be great.


texts: Postgres: How is SET NOT NULL "more efficient" than CHECK constraint

texts:
In PostgreSQL docs for Constraints, it says

  A not-null constraint is functionally equivalent to creating a check constraint CHECK (column_name IS NOT NULL), but in PostgreSQL creating an explicit not-null constraint is more efficient.

I'm wondering

What exactly does it mean by "more efficient"?
What are the detriments of using CHECK (column_name IS NOT NULL) instead of SET NOT NULL?

I want to be able add a NOT VALID CHECK constraint and validate it separately (so the AccessExclusiveLock is only held for a short period of time for the adding of the constraint and then a ShareUpdateExclusiveLock is held for the longer validation step):

ALTER TABLE table_name
  ADD CONSTRAINT column_constraint
  CHECK (column_name IS NOT NULL)
  NOT VALID;
ALTER TABLE table_name
  VALIDATE CONSTRAINT column_constraint;

Instead of:

ALTER TABLE table_name
  ALTER COLUMN column_name
  SET NOT NULL;


texts: If there is no end-user input, is it safe to un-parameterize a parameterized query to avoid parameter sniffing?

texts:
AFAIK, a parameterized query is commonly used to prevent SQL injection attacks. What if there is no end-user input that can cause SQL injection? In this case, is it safe to un-parameterize a parameterized query to avoid parameter sniffing?

If I need to specify RDBMS, I'm talking about SQL Server and MySQL.

For example, changing the following query:

SELECT * FROM Foo WHERE name = #{name}

into this:

SELECT * FROM Foo WHERE name = 'John' /* The literal value('John') can be changed as the query is generated */


texts: Update various tables depending on survey answer

texts:
I've created a survey solution and it work just fine. All questions have multiple options to choose from as answer. What I would like to achieve is when a certain option is saved as an answer the value or values of a another table is to be updated. The table or tables that are to be updated may vary depending on the answer of the user and the data type of column might also vary.

I was thinking about using a metadata table, in this example AffectedData, containing information about the tables, the values and data types. Something like this pseudo code:

CREATE TABLE AffectedData
    ID int
    TableName nvarchar(50) NOT NULL -- table to update
    ColumnName nvarchar(50) NOT NULL -- the col to update
    DataType nvarchar(50) NOT NULL -- the col data type
    DataValue nvarchar(50) NOT NULL -- the value to update with
    Operation int --insert or update

When an answer is inserted a trigger or a procedure might query this table and by using some conditional logic cast the DataValue to the correct data type, using DataType as hint, and insert or update into to the table specified in TableName. In other words I assume I have to use dynamic SQL, using sp_executesql to execute the statement.

Is this a way to go or is there some other wise way of achieving this?


texts: Troubles connecting to a SQL Server Express instance on AWS

texts:
I'm trying to remote connect a SQL Server Express instance that is running on AWS on a db.t2.micro. I'm getting the following error:

  A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - The requested name is valid, but no data of the requested type was found.) (Microsoft SQL Server, Error: 11004)

I'm behind a proxy, but I should have route the address so the proxy is bypass for the IP of the SQL Server Express instance. What else can I try?


texts: Storing sensitive information in a SQL Server project

texts:
I am very new with encryption in SQL Server so I am stumbling over things that require prior experience. I have done my googling, but I need reassurance so here we go:

Scenario

I have a SQL Server Project (*.sqlproj) where my team and I put all of our:

schema related changes
post deployment scripts
seed scripts

Best practices?

However I have run into a best practices situation:

Should I store my MASTER KEY in my SQL Project?

I'm leaning towards no because of the password.

Should I store my CERTIFICATE in my SQL Project?

Seems okay to me

Should I store my SYMMETRIC KEY in my SQL Project?

Seems okay to me

Overall

Should these sensitive items be a part of my SQL Server Project and checked into source control?

It is usually best practice to NOT store anything with a password in Source Control.

Does the Master Key qualify for what I am talking about?

On a side note - I am not storing column level seed data passwords in source control. I have already decided this would be a bad idea.

Example for context

I am pulling these examples from Microsoft's examples here.

USE AdventureWorks2012;
GO

-- 1. Should this be stored in my SQL Server Project? The password concerns me.
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = '<some strong password>';
GO

-- 2. Should this be stored in my SQL Server Project? Seems harmless.
CREATE CERTIFICATE HumanResources037
   WITH SUBJECT = 'Employee Social Security Numbers';
GO

-- 3. Should this be stored in my SQL Server Project? Seems harmless.
CREATE SYMMETRIC KEY SSN_Key_01
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE HumanResources037;


texts: How can I resolve SSIS Excel Connection Manager Error 0xC0209303?

texts:
I have created an SSIS package that imports an excel file into a SQL Server table.

The SSIS package runs without any problem when I run it locally on my machine but when I run it on the server where the package will be scheduled I get the below error (from a text file I am outputting errors to using SSIS logging).

After researching, the only recommendations I could find was to set the Run64BitRuntime property to false which I did but still no luck. I doubt this is what is causing my error though because the error is not specifying anything regarding 64 bit (as was the case in the articles I found).

I also thought that it might be that the Server does not have the appropriate Excel drivers but I don't think that is the case either because usually the error message would say something about the drivers not being registered.

I currently don't have access to remote into the server. I can only upload the package to a folder and then it is ran by an application so the only error messages I can see are what is in the text error log I have created.

  Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The
  AcquireConnection method call to the connection manager "Envision"
  failed with error code 0xC0209303.  There may be error messages posted
  before this with more information on why the AcquireConnection method
  call failed.

"Envision" is the name of my excel connection manager.

I populate the Excel File Path and the Connection String using expressions.

The connection string expression looks like this:

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+ @[User::SourceFilePath] +";Extended Properties=\"EXCEL 12.0 XML;HDR=YES\";"

The SSIS Pacakge is executed by a windows username/account. I think it might be a web services account. (BDS_sprtIIS)

Does anyone have any solutions or suggestions as to how to resolve this issue of the package only working on my local machine but not on the actual server where the package will be deployed to?

I found the below answer on another forum, could it be what is causing my issues? They are basically saying that the Excel connection manager tries to access the users temp folder for some reason and if it does not have access to that folder then it will fail:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/da77919c-0161-4eb5-bf89-7107d839435a/the-acquireconnection-method-call-to-the-connection-manager-excel-connection-manager-failed-with?forum=sqlintegrationservices

  I noticed too that the Microsoft.JET.OLEDB.4.0 driver will try to read
  the temp directory underneath the profile of the logged in user.

.

  ... We run our SQL Agents using a lower
  level domain account and run our SSIS packages using a Proxy Account.
  You are correct as Procmon confirmed it for me as well.  I gave the
  Proxy Account rights to the profile's temp directory (C:\Documents and
  Settings\SQLAgentDomainAccount\Local Settings\Temp) and it worked!

I am not using using SQL Server Jobs or Proxy accounts. The package is simply executed by a windows account most likely thru a command line script.

The windows account has access to the file but I am not sure if it has access to it's "TEMP" folder (which I do not ever reference in the package so I don't know why it would need to have access to that folder)...


texts: Is pre-sorting valuable when using table value constructor to insert multiple values into a table with clustered index?

texts:
This is related to this question: Efficient INSERT INTO a Table With Clustered Index

From that question it sounds like SQL Server will sometimes pre-sort the data when inserting multiple values into a table with a clustered index to reduce fragmentation. Will SQL Server also do this when I use a Table Value Constructor to insert multiple values in an insert statement?

For example, if I have a table event_log that is clustered on a timestamp column and I insert data like this:

INSERT INTO event_log (timestamp, data)
VALUES
('03:00:02', 'data string...'),
('03:00:01', 'data string...'),
('03:00:00', 'data string...')

Will SQL Server pre-sort for me to ensure the data goes in efficiently without fragmentation or should I sort the data in my app before generating the SQL?


texts:
SELECT *
FROM my_players
WHERE player_type = 'IF'
ORDER BY cardweight / rand() DESC limit 3

That is my current query, What I am trying to do is select random rows for which I have the * in my query, so I am trying to get the psbin (which is a column in my table) it is a int and I want the psbin of all these rows combined not to go over 250k.

Create table statement is:

CREATE TABLE `my_players` (
`myplayer_id` int(11) NOT NULL AUTO_INCREMENT,
`player_id` int(11) NOT NULL,
`First_Name` varchar(255) NOT NULL,
`Last_Name` varchar(255) NOT NULL,
`Rare` varchar(255) NOT NULL,
`Foot` varchar(255) NOT NULL,
`Height` varchar(255) NOT NULL,
`Date_Of_Birth` varchar(255) NOT NULL,
`player_name` varchar(255) NOT NULL,
`player_nation` int(11) NOT NULL,
`player_club` int(11) NOT NULL,
`player_league` int(11) NOT NULL,
`player_type` varchar(255) NOT NULL DEFAULT 'GOLD',
`player_pos` varchar(255) NOT NULL,
`totwImage` varchar(255) DEFAULT NULL,
`psbin` int(11) NOT NULL,
`xboxbin` int(11) NOT NULL,
`ps3bin` int(11) NOT NULL,
`360bin` int(11) NOT NULL,
`xmin` int(11) NOT NULL,
`xmax` int(11) NOT NULL,
`pmin` int(11) NOT NULL,
`pmax` int(11) NOT NULL,
`cardweight` int(11) NOT NULL DEFAULT '1',
`definitionId` int(21) NOT NULL,
`resourceId` int(21) NOT NULL,
`player_added` datetime NOT NULL,
`player_status` enum('0','1') NOT NULL,
`player_rating` int(11) NOT NULL,
`Attribute1` int(11) NOT NULL,
`Attribute2` int(11) NOT NULL,
`Attribute3` int(11) NOT NULL,
`Attribute4` int(11) NOT NULL,
`Attribute5` int(11) NOT NULL,
`Attribute6` int(11) NOT NULL,
`available` int(11) NOT NULL,
PRIMARY KEY (`myplayer_id`)
) ENGINE=MyISAM AUTO_INCREMENT=3207 DEFAULT CHARSET=latin1


texts: InnoDB engine getting disabled after decreasing innodb_page_size to 4K

texts:
I'm trying to optimize my database (mariadb) for high write (insert & update).
Database is running fine with default  innodb_page_size (16K), but when I decrease the value of innodb_page_size=4K.
Database server starts successfully, but getting error while creating table on InnoDB engine.
Error Code: 1286. Unknown storage engine 'InnoDB'
Here is my.cnf
[mysqld]
log-error=/var/lib/mysql/mysql-3.kannel.com.err
performance-schema=0

max_allowed_packet=268435456

event_scheduler = ON
#innodb_page_size=16384
#innodb_page_size=8192
innodb_page_size=4K
innodb_file_per_table=4
innodb_buffer_pool_size=3G
innodb_buffer_pool_instances=2
innodb_log_file_size=1024M
innodb_log_files_in_group=4
innodb_log_buffer_size=64M
innodb_log_write_ahead_size=64M
innodb_flush_log_at_trx_commit =2
innodb_read_io_threads=32
innodb_write_io_threads=128
innodb_io_capacity=10000
innodb_thread_concurrency=2
innodb_flush_method=o_direct

join_buffer_size=512M
sort_buffer_size=20M
read_rnd_buffer_size=200M
datadir=/var/lib/mysql
max_allowed_packet=26843545600
#open_files_limit=10000
default-storage-engine=MyISAM
#default-storage-engine=InnoDB
max_connections = 500

Thanks!


texts:
I am having issues getting my subquery to run properly.  Everything runs quickly and correctly until I add the subquery.  I get the error:
"Msg 4145, Level 15, State 1, Line 66
An expression of non-boolean type specified in a context where a condition is expected, near ','."

FROM FactPerson FP
JOIN FactCrash FC ON FP.CrashSK = FC.CrashPK
JOIN DimHWYType DHT ON DHT.HWYTypeCode = FC.HighwayTypeCode
JOIN FactVehicle FV ON FV.CrashSK = FC.CrashPK
JOIN DimVehicleType DVT ON DVT.VehicleTypeCode = FV.VehicleTypeCode
WHERE (FC.HighwayTypeCode = 'A' OR FC.HighwayTypeCode = 'B' OR FC.HighwayTypeCode = 'C')
AND (FV.VehicleTypeCode = 'A' OR FV.VehicleTypeCode = 'B' OR FV.VehicleTypeCode = 'S')
AND (FP.Age, FP.PersonType) IN
    (SELECT FP.Age, FP.PersonType
    FROM FactPerson FP
    WHERE (FP.PersonType = 'A')
    AND (FP.Age BETWEEN 18 AND 21))

I've also tried EXISTS, but it returns records that are outside the Age criteria.

FROM FactPerson FP
JOIN FactCrash FC ON FP.CrashSK = FC.CrashPK
JOIN DimHWYType DHT ON DHT.HWYTypeCode = FC.HighwayTypeCode
JOIN FactVehicle FV ON FV.CrashSK = FC.CrashPK
JOIN DimVehicleType DVT ON DVT.VehicleTypeCode = FV.VehicleTypeCode
WHERE (FC.HighwayTypeCode = 'A' OR FC.HighwayTypeCode = 'B' OR FC.HighwayTypeCode = 'C')
AND (FV.VehicleTypeCode = 'A' OR FV.VehicleTypeCode = 'B' OR FV.VehicleTypeCode = 'S')
AND EXISTS
    (SELECT FP.Age, FP.PersonType
    FROM FactPerson FP
    WHERE (FP.PersonType = 'A')
    AND (FP.Age BETWEEN 18 AND 21))


texts: High Cardinality, Time Series Data.. what DB should I use?

texts:
I have a system that I'm building based on ElasticSearch. It works great, except I'm having trouble with one of my facets. I've got about 10 million documents, and each document has 1 to 200 interesting phrases that have been detected in the document.  I have a facet that I use to calculate the "most common interesting phrases" and this times out.

The reason is that this has high cardinality. There are very few of these that happen more than 1 time. But, the ones that do, I need to count. And, I need to be able to query this based on interesting phrases in the last day, week, month, quarter.  So, the counts need to be time-based.

I'm looking at a number of alternatives to solve this. I've considered using Graphite, but I'm not sure of the high cardinality.  I've manually paged through all it of it in ES, and built it myself. But, that seems like overkill.

And, realistically, we need to do this type of counting for more than just the one series. This is the worst offender, but we do this with a lot of items.

Any thoughts on a better database to use?  Or, would Graphite do this well?

Again, we're typically pulling the top 10/20/50 phrases over a timespan in high cardinality data.

EDIT

For those of you asking.. here's the ES query.

curl -X GET '1.2.3.4:9200/mentions/mention/_search?search_type=count&size=10&pretty' -d '{
  "query":{
    "match":{
      "keyword":{
        "query":[
          "hair accessories"
        ]
      }
    }
  },
  "facets":{
    "phrases":{
      "terms":{
        "field":"phrases",
        "size":10,
        "all_terms":false
      }
    }
  },
  "size":10}'


texts: Is there any other ways to access the error log in MySQL on ubuntu other than using SSH? I really need to access the error log, but I do not have any privileged to connect via SSH. Please help me.  Thanks


texts: SQL Partioning on date, how does it work for future records?

texts:
I am setting up a new system and I would like to partition the data for speed. Basically what I would like to do is have one database with all the latest information (e.g. last 3 months). then have some partitioned tables for data older than that. Have not chosen mysql or sql server express as not sure which one has the desired capabilities.

I have read some examples on how partitioning on ranges works as seen below:

CREATE TABLE SAMPLE_ORDERS
(
    ORDER_NUMBER NUMBER,
    ORDER_DATE DATE,
    CUST_NUM NUMBER,
    TOTAL_PRICE NUMBER,
    TOTAL_TAX NUMBER,
    TOTAL_SHIPPING NUMBER
)
PARTITION BY RANGE(ORDER_DATE)
(
    PARTITION SO99Q1 VALUES LESS THAN TO_DATE(‘01-APR-2010’, ‘DD-MON-YYYY’),
    PARTITION SO99Q2 VALUES LESS THAN TO_DATE(‘01-JUL-2010’, ‘DD-MON-YYYY’),
    PARTITION SO99Q3 VALUES LESS THAN TO_DATE(‘01-OCT-2010’, ‘DD-MON-YYYY’),
    PARTITION SO99Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2011’, ‘DD-MON-YYYY’),
    PARTITION SO00Q1 VALUES LESS THAN TO_DATE(‘01-APR-2011’, ‘DD-MON-YYYY’),
    PARTITION SO00Q2 VALUES LESS THAN TO_DATE(‘01-JUL-2011’, ‘DD-MON-YYYY’),
    PARTITION SO00Q3 VALUES LESS THAN TO_DATE(‘01-OCT-2011’, ‘DD-MON-YYYY’),
    PARTITION SO00Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2012’, ‘DD-MON-YYYY’)
);

Read more at http://www.devarticles.com/c/a/Oracle/Partitioning-in-Oracle/1/#YCTAmfR0hkvo1at0.99

But this seems to be more for partitioning older data. What happens when it reaches newer data? Will it put the new data into a single partition?

I would rather have a small amount of data in the new table and dump all the old information.

Do I just need to create multiple partitions up to 2020, and keep manually adding them as the project runs?

Is there another way to do this?

I know I could achieve what I need by replicating all the data into another table, and cleaning up the old data every night, however I have had issues with this before and was wondering if partitioning would do a better job?


texts:
I am trying to create a STORED PROCEDURE that will be used to UPDATE a table called machine. This table has three columns (machine_id, machine_name and reg_id).

In aforementioned table,reg_id (INT) is a column whose values can be changed for a machine_id.

I would like to define a QUERY/PROCEDURE to check if a reg_id already exists in that table. If it does, then UPDATE that row, otherwise INSERT a new row.

Can someone please help me to write that QUERY/PROCEDURE?


texts: Cannot start SqlLocalDB instance with my Windows account

texts:
I'm the administrator and simply run the command:

 sqllocaldb start v11.0

Result:

 Start of LocalDB instance "v11.0" failed because of the following error:
 Error occurred during LocalDB instance startup: SQL Server process failed to sta
 rt.

Event viewer log
Event ID: 528

  Windows API call WaitForMultipleObjects returned error code: 575.
  Windows system error message is: {Application Error} The application
  was unable to start correctly (0x%lx). Click OK to close the
  application. Reported at line: 3621.

I tried another (user and administrator) accounts, there were no problems with them.

I uninstalled & reinstalled 2012 version of SQLLocalDB.msi but I had no luck. Do you have any idea & fix?


texts: How do I guarantee inventory numbers are unique across all tables?

texts:
I have an assortment of physical objects which need to be inventoried and each type of object has it's own table. For two example objects, let's use 'swords' for the table containing different swords to be inventoried and 'butters' for different butters. Obviously, these two objects have practically nothing in common as far as attributes other than the fact that they both need to be accounted for in the inventory. This is why they each will have their own table.

I need every inventoried object to have a unique, integer ID assigned to it. So, inventory number '5' might be a sword and '6' might be a butter.

I believe what I need is a separate table which just keeps track of inventory numbers, 'inv_nums'. Then I'd have a foreign key constraint referencing that table in each of the 'swords' and 'butters' tables. The relationship would be 1:1 between an object table and the 'inv_nums' table.

How do I guarantee that an inventory number doesn't get used, inadvertently or otherwise, by multiple object tables?

I'm thinking maybe I need to use a trigger on inserts which calls a function or procedure (not sure which) to either create and return the next sequential inventory number or ensure that the one being provided in the insert statement isn't already in use (it doesn't already exist in the 'inv_num' table).

Am I on the right track? I'm sure there must be a design pattern for this somewhere but my googling hasn't been very successful thus far. I could certainly do all this at the application layer but I'd rather have it enforced in the DB itself.

I'm implementing this in MySQL/MariaDB but I think the concept behind this should be the same no matter the (relational) DB.


texts: Replication Broken, List Out of Sync Tables

texts:
In a master-slave setup on MySQL 5.5 on RHEL 6.1, when db replication breaks (for whatever reason), what is a good way to:

check (and alert) replication is broken?
get a list of tables that are out of synch across various databases?

I don't have access to server logs, but can query the tables under "mysql" db and "information_schema", in addition to the dbs used by the applications.


texts: SSIS package hits "login failed for user" when simultaneous operations occur against the same target database

texts:
Quick summary

We have been working on migrating our library of SSIS packages from SQL Server 2008 hosted on physical Windows Server 2003 to SQL Server 2012 hosted on virtual Windows Server 2012.  The new packages we deploy using project deployment to an Integration Services Catalog.  The packages import data into another SQL Server 2012 database on a separate server/instance.

It seems we get a "Login failed" error when the package is attempting to execute multiple simultaneous SQL operations against the same SQL Server 2012 target database.

Quick comparison of environments

OLD (where it works)

Target database is SQL Server 2008, on physical Windows Server 2003.
SSIS 2008 packages executed from file system on Windows Server 2003; receive parameters from machine environment variables.

NEW (where we sometimes get the error)

Target database is SQL Server 2012, on virtual Windows Server 2012.
SSIS 2012 packages executed from SQL 2012 Integration Services Catalog on separate server: also virtual Windows Server 2012.

The error

An unusual error is occurring for some packages in the new environment:

DTS_E_OLEDBERROR
An OLEDB error has occurred
Error code: 0x80040E4D
An OLEDB record is available.
Source: "Microsoft SQL Server Native Client 11.0"
HResult: 0x80040E4D
Description: "Login failed for user 'MyUser'"

In the target database SQL log, we have this entry:

Source: Logon, Message: Login failed for user 'MyUser'. Reason: Password did not match that for the login provided. [CLIENT: {IP addr. for SSIS DB server}]

While this appears cut and dried, it does not tell the whole story.

What we know

First of all, we double-checked the database connection strings over in the SSIS package. The packages have database connection managers whose connection properties are bound to project parameters, which in turn are bound to environment variables in the Integration Services Catalogs.

We verified that the packages are executed using the correct environment.

Furthermore, SQL operations using the same connection manager work fine leading up to the error!

The common factor in all the errors seems to be that when multiple simultaneous SQL operations occur using the same database connection manager, we get this "login failed" error.

When we refactor a failing portion of a package to execute database operations one at a time instead of simultaneously, the error goes away.

But it worked in our old environment...

These packages worked fine in SSIS 2008 targeting a SQL Server 2008 database, running on physical Windows Server 2003 servers.

...And it still works when we run the packages from Visual Studio 2012

When we run the new SSIS 2012 packages from Visual Studio against the same target database (SQL 2012 on virtual Windows Server 2012), we do not encounter the error.

This leads us to believe it has something to do with how they are deployed and run in the Integration Services Catalog, but we cannot figure out what is wrong.  Here is where we could use some help.


texts: I am wondering if anyone has tried a cascading pgbouncer setup, where pgbouncer A connects to pgbouncer B and that in turn connects to postgres.  From 1Km away this seems a possible setup, but I am wondering if anyone has ever tried this and if they stumbled upon problems other than maximum connections and how to calculate them.


texts: is there any advantage when troubleshooting service broker, to create the queue with retention=ON?

texts:
from CREATE QUEUE (Transact-SQL):

  RETENTION Specifies the retention setting for the queue. If RETENTION
  = ON, all messages sent or received on conversations that use this queue are retained in the queue until the conversations have ended.
  This lets you retain messages for auditing purposes, or to perform
  compensating transactions if an error occurs. If this clause is not
  specified, the retention setting defaults to OFF.

when I have to do some Service Broker Troubleshooting

I have a look at these:

-- Message Types
SELECT *
FROM sys.service_message_types;
-- Contracts
SELECT *
FROM sys.service_contracts;

-- Queues
SELECT *
FROM sys.service_queues;

-- Services
SELECT *
FROM sys.services;

-- Endpoints
SELECT *
FROM sys.endpoints;

I check this:

SELECT conversation_handle, is_initiator, s.name as 'local service',
far_service, sc.name 'contract', state_desc
FROM sys.conversation_endpoints ce
LEFT JOIN sys.services s
ON ce.service_id = s.service_id
LEFT JOIN sys.service_contracts sc
ON ce.service_contract_id = sc.service_contract_id;

-- Error messages in the queue
SELECT *
FROM sys.transmission_queue;

QUESTION:
Is there any advantage in setting RETENTION=ON for service broker queues?

as further info:
Where would the message be stored?
For how long?

thank you

Recommendations on how to organize Queues in Service Broker

The following Chinese article shows an example of retention=on:

在创建QUEUE的时候，指定retention也很重要。。

如果retention=on.那么即使message被receive之后，还是留在了queue里面，直到这个conversation被end

ALTER QUEUE [dbo].[targetQueue] WITH STATUS = ON , RETENTION = ON , POISON_MESSAGE_HANDLING (STATUS = ON)

作者：dbLenis

来源：CSDN

原文：https://blog.csdn.net/wujiandao/article/details/7288864

版权声明：本文为博主原创文章，转载请附上博文链接！


texts: AlwaysOn database not synchronizing/recovery pending due to cluster loss quorum

texts:
I have this error in cluster event

  'Cluster node 'MMPDB1NEW' was removed from the active failover cluster membership. The Cluster service on this node may have stopped. This could also be due to the node having lost communication with other active nodes in the failover cluster'

But now even though my cluster nodes are online and quorum is healthy, still my node 2 database is showing not synchronizing/Recovery pending and in AlwaysOn health events I can see:

  'A connection for availability group 'MTCBAG' from availability replica 'node1' with id.. to 'node 2' with id.. has been successfully established.

Should I need to wait until it synchronizes back or anything need to be done to bring back my secondary node?


texts: Can we migrate oracle database 10gR2 to cross platform

texts:
I want to know can we migrate Linux based oracle database 10gR2 (10.2.0.1.0) to windows server 2003 with oracle database 10gR2 (10.2.0.3.0). I have seen the documentation related to database migration where i don't understand the compatibility section. Is this important in migration that both host and target database versions and patch level should be same.
Please help me.

this is the output that i get when i run the transport.sql to migrate my database from Linux to Windows

E:\new\test>set ORACLE_SID=newdb
E:\new\test>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Dec 19 20:49:08 2014

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> @transport.sql
ORACLE instance started.

Total System Global Area  452984832 bytes
Fixed Size                  1291120 bytes
Variable Size             150998160 bytes
Database Buffers          293601280 bytes
Redo Buffers                7094272 bytes

Control file created.

ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

ERROR:
ORA-03114: not connected to ORACLE

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* Your database has been created successfully!
* There are many things to think about for the new database. Here
* is a checklist to help you stay on track:
* 1. You may want to redefine the location of the directory objects.
* 2. You may want to change the internal database identifier (DBID)
*    or the global database name for this database. Use the
*    NEWDBID Utility (nid). ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SQL>
SQL> WHENEVER SQLERROR EXIT;
SQL>
SQL> DOC
DOC>#######################################################################
DOC>#######################################################################
DOC>   The following statement will cause an "ORA-01722: invalid number"
DOC>   error if there the database was not opened in UPGRADE mode
DOC>
DOC>   If you encounter this error, execute "SHUTDOWN", "STARTUP UPGRADE" and
DOC>   re-execute utlirp.sql
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL> SELECT TO_NUMBER('MUST_BE_OPEN_UPGRADE') FROM v$instance
2  WHERE status != 'OPEN MIGRATE';
ERROR:
ORA-03114: not connected to ORACLE

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Pr
oduction With the Partitioning, OLAP and Data Mining options


texts: MySQL intersection on one table with time type

texts:
I've a table with 4 columns: id (int), name (varchar), timefrom (time), timeuntil (time)

Now I would like to have all combinations where they have an intersection on the timefrom to timeuntil.

My current solution is that I load all the records and loop it in php for every full hour... but this costs me much performance.

Any Ideas, is this possible to solve in a Select?

I want to know which presences are sharing the same timefrom, timeuntil As an example:

#1 / whole day / 06:00:00 / 18:00:00
#2 / morning   / 07:00:00 / 12:00:00
#3 / afternoon / 13:00:00 / 18:00:00

I would like to have the result:

#1, #2, #3   (the whole day shares the times with morning, afternoon)
#2, #1       (the morning shares the times with the whole day)
#3, #1       (the afternoon shares the times with the whole day)


texts: How to copy user rights and roles from one user to another

texts:
I require a PL/SQL script that copies all of a user's rights, roles and mandates from one user to another.

I am using Oracle 12c.


texts: SQL Server 2016 - Is the Microsoft R Client any different from a standalone Microsoft R Server?

texts:
Along with the integrated R services on SQL Server 2016, Microsoft is also offering enterprise level Microsoft R Server as a standalone installation.
Some of the documentation on MSDN shows that the standalone MS R Server is different from a data science Microsoft R Client.
This is a picture from the MSDN page (bottom left): https://msdn.microsoft.com/en-us/library/mt696069.aspx

The data science client link takes you here: https://msdn.microsoft.com/en-us/library/mt696067.aspx

But the set-up wizard comments as if they're both the same thing.

If anyone has any clarity on this, please let know.


texts:
I have the following PG (12) reservations table

id               uuid
reservable_id    uuid
reserved_between tsrange

How can I ensure there are no 2 reservations for the same reservable_id overlapping each other?

A "simple" UNIQUE INDEX on reservable_id,reserved_between works only (as expected) only with equal ranges.


texts: What can cause an Index "total writer" to be the same in every index?

texts:
I have a table, with some Indexes ( created by me, others by SQL Server Profiler (SqlServer 2008)).

I use this query to analyze unused Indexes:

--Possible bad Indexes (writes > reads)
SELECT  OBJECT_NAME(s.object_id) AS 'Table Name',
        i.name AS 'Index Name',
        i.index_id,
        user_updates AS 'Total Writes',
        user_seeks + user_scans + user_lookups AS 'Total Reads',
        user_updates - ( user_seeks + user_scans + user_lookups ) AS 'Difference'
FROM    sys.dm_db_index_usage_stats AS s WITH ( NOLOCK )
        INNER JOIN sys.indexes AS i WITH ( NOLOCK ) ON s.object_id = i.object_id
                                                       AND i.index_id = s.index_id
WHERE   OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
        AND s.database_id = DB_ID()
        AND user_updates > ( user_seeks + user_scans + user_lookups )
        AND i.index_id > 1
ORDER BY 'Difference' DESC,
        'Total Writes' DESC,
        'Total Reads' ASC ;

But what I see, is that 14 Indexes ( yes i'm studing to disable them ) have the same "total writes". If I hold F5, i can se that they're used in "writes" at the same time.

Total reads are different.

What is the reason of this? why every index is updated ( write ) at the same time and same ammount, But with different reads?

Didn't found anything like this on web or stack.

Thanks


texts: SQL Server migration from 2005/2008 to 2012

texts:
We need to upgrade two SQL Servers for our projects to SQL Server 2012.
Since we’re not db-admins or familiar with such migrations, I wanted to ask you if you could tell me what we have to be aware of and check before upgrading.

Some information about the servers:

Project 1:

SQL Server 2008
No procedures
No functions
No views

I think this should not really be a problem to upgrade to 2012 right? Is there anything else we have to make sure?

Project 2:

SQL Server 2005
~700 procedures
~50 functions
~100 views

I think this will be more of a problem… is there any logic that won’t work on SQL Server 2012 that did on SQL Server 2005?

Is there a list of keywords which are not supported anymore?

Are there other problems that could happen during / after the migration?

Thanks in advance


texts:
Error Log: MYSQLD log

140925 20:47:19 mysqld_safe Number of processes running now: 0
140925 20:47:19 mysqld_safe mysqld restarted
140925 20:47:26  InnoDB: Initializing buffer pool, size = 56.0M
140925 20:47:26  InnoDB: Completed initialization of buffer pool
InnoDB: **The log sequence number in ibdata files does not match**
InnoDB: **the log sequence number in the ib_logfiles!**
140925 20:47:26  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
140925 20:47:32  InnoDB: Started; log sequence number 4 3219462995
140925 20:47:32 [Note] Event Scheduler: Loaded 0 events
140925 20:47:32 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.71'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution
Messages Log :

Sep 25 08:15:02 s1 kernel: Out of memory: Kill process 28796 (php-cgi) score 18 or sacrifice child
Sep 25 08:15:02 s1 kernel: Killed process 28796, UID 528, (php-cgi) total-vm:340760kB, anon-         rss:40048kB, file-rss:21088kB
Sep 25 08:15:03 s1 kernel: php-cgi invoked oom-killer: gfp_mask=0x280da, order=0, oom_adj=0,     oom_score_adj=0
Sep 25 08:15:03 s1 kernel: php-cgi cpuset=/ mems_allowed=0

Server Details:
Operating system: Centos 6.2 linux server,
Memory: 4GB
Swap Size: 2GB

Steps taken to resolve the Issue:

Enabled innodb_force_recovery=4 to commit the .ibd files and to mysql server.
Tried installing MYSQLTUNER to monitor query activities.However cudnt install it.

Behavior

Have to manually restart it after it shuts down. Not able to restart it automatically using mysqlsafe
Have been seeing this issue since past few days and also a suspect spike in number of hits which reflects in my AW stats.
Will appreciate if someone help me know where to look for solutions and help me configure the mysql config file for a proper performance and memory usage.

My.conf settings:

[mysqld] datadir=/var/lib/
mysql socket=/var/lib/mysql/mysql.sock
 user=mysql
 symbolic-links=0
 innodb_file_per_table = 1
 innodb_buffer_pool_size = 56M
 thread_concurrency = 8
 query_cache_size = 32M
 thread_cache_size = 8
 myisam_sort_buffer_size = 64M
 read_rnd_buffer_size = 8M
 read_buffer_size = 2M
 sort_buffer_size = 2M
 table_cache = 512
 max_allowed_packet = 1M
 key_buffer = 384M
 [mysqld_safe] log-error=/var/log/mysqld.log
 pid-file=/var/run/mysqld/mysqld.pid


texts: Getting started with AWS: Price for large databases?

texts: At the moment, I have a database that isn't accessed very often, but has 400 million rows and totals out to be 100gb disk space. I'm worried that, despite the low usage, I'll have to shell out for a near enterprise-level database instance in order to get the storage I need. How does storage and database usage affect pricing? I've been looking at pricing on the AWS site for a few weeks now and honestly still don't know how to estimate how much this database will cost. Any help is appreciated.


texts: How to list Oracle objects that have not been used in the last month?

texts:
I have an Oracle database that has more than 1000 tables, sequences, indexes, and other object.

I want to clean up database object. Some objects are not used.

I want to list all objects that have not been use in more than 1 month so I can delete them.


texts:
We are writing queries for our website, and when we test the queries in a dev environment, the queries run pretty fast. The problem that we are having is the dev environment has less that 10 active users on it, while our live environment has 4,000+ simultaneous users and then our server just cant handle the queries.

So, is there a tool out there that can take some queries and do some stress testing on them, so we can test the queries before we put them live and kill the server?


texts:
I would like to have a table pattern on my db. I.e. every column should have

Record_Insert_Date
Record_LastChange_Date
Record_User_Insert
Record_User_Update

The obvious approach is to add these columns on each tables, but is not so easy to mantain. I would like to have this informations shared across db in a single table. Schema of this table should be somthing like

FOREIGN_TABLE_ID
FOREIGN_ROW_ID
Record_Insert_Date
Record_LastChange_Date
Record_User_Insert
Record_User_Update

The questions are

how to join with other tables (two possible options are)

1) Use sys.tables table id as FOREIGN TABLE ID. May change? Should be dangerous in case of db refactory? Performance of query on sys.tables to retreive table name to join dynamically?
2) Add an extra field each table on db to have an integer code that identify it. I.e tableA.TABLE_ID=1, tableB.TABLE_ID=2, ..., tableXX.TABLE_ID=N. Using this approach a simple join is enough to match record. Difficult to use is a table have a multiple key.
3) REVERSE OF 2) Add an extra field each table on db to have an FK to shared table. In this case shared table have an IDENTITY() key instead of (FOREIGN_TABLE_ID, FOREIGN_ROW_ID)

performance of each approach?


texts: What's difference between MySql IN and ANY operator?

texts:
Data

Dump

CREATE TABLE `customers` (
  `customer_id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `birth_date` date DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `address` varchar(50) NOT NULL,
  `city` varchar(50) NOT NULL,
  `state` char(2) NOT NULL,
  `points` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`customer_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4;
INSERT INTO `customers` VALUES (1,'Babara','MacCaffrey','1986-03-28','781-932-9754','0 Sage Terrace','Waltham','MA',2273);
INSERT INTO `customers` VALUES (2,'Ines','Brushfield','1986-04-13','804-427-9456','14187 Commercial Trail','Hampton','VA',947);
INSERT INTO `customers` VALUES (3,'Freddi','Boagey','1985-02-07','719-724-7869','251 Springs Junction','Colorado Springs','CO',2967);
INSERT INTO `customers` VALUES (4,'Ambur','Roseburgh','1974-04-14','407-231-8017','30 Arapahoe Terrace','Orlando','FL',457);
INSERT INTO `customers` VALUES (5,'Clemmie','Betchley','1973-11-07',NULL,'5 Spohn Circle','Arlington','TX',3675);
INSERT INTO `customers` VALUES (6,'Elka','Twiddell','1991-09-04','312-480-8498','7 Manley Drive','Chicago','IL',3073);
INSERT INTO `customers` VALUES (7,'Ilene','Dowson','1964-08-30','615-641-4759','50 Lillian Crossing','Nashville','TN',1672);
INSERT INTO `customers` VALUES (8,'Thacher','Naseby','1993-07-17','941-527-3977','538 Mosinee Center','Sarasota','FL',205);
INSERT INTO `customers` VALUES (9,'Romola','Rumgay','1992-05-23','559-181-3744','3520 Ohio Trail','Visalia','CA',1486);
INSERT INTO `customers` VALUES (10,'Levy','Mynett','1969-10-13','404-246-3370','68 Lawn Avenue','Atlanta','GA',796);

Following works fine

SELECT * FROM sql_store.customers
where customer_id in(
    1,2
);

and

SELECT * FROM sql_store.customers
where customer_id = any(
    select 1
);

but following not

SELECT * FROM sql_store.customers
where customer_id = any(
    1,2
);

and

SELECT * FROM sql_store.customers
where customer_id in(
    select 1,2
);

Is ANY made to be used with subqueries which returns table and IN which returns list?


texts:
I have a stored procedure that takes around 2 minutes to execute. The execution plan suggested me to create a non clustered index on a table (which is a high traffic table with millions of records in it, plus it gets a constant stream of data every single second).

Please help me decide on whether I should create an index on that table or not.

Note: I tried creating a non clustered index on that table (on development server) and the time was reduced to 40 seconds from 2 minutes.


texts: Does the order of columns in a PK index matter?

texts:
I have a few very large tables with the same basic strucure.  Each one has a RowNumber (bigint) and DataDate (date) column.  Data is loaded using SQLBulkImport every night, and no "new" data is ever loaded - its a historical record (SQL Standard, not Enterprise, so no partitioning).

Because each bit of data needs to be tied back to other systems, and each RowNumber/DataDate combination is unique, that is my Primary Key.

I notice that due to the way I defined the PK in SSMS Table Designer, RowNumber is listed first and DataDate second.

I also notice that my fragmentation is always VERY high ~99%.

Now, because each DataDate only appears once, I would expect the indexer to just add to the pages each day, but I wonder if it actually is indexing based on RowNumber first, and hence having to shift everything else around?

Rownumber is not an identity column, it's an int generated by an external system (sadly). It resets at the start of each DataDate.

Example Data

RowNumber | DataDate | a | b | c.....
   1      |2013-08-01| x | y | z
   2      |2013-08-01| x | y | z
...
   1      |2013-08-02| x | y | z
   2      |2013-08-02| x | y | z
...

Data is being loaded in RowNumber order, one DataDate per load.

Import process is bcp - I have tried loading to a temp table and then selecting in order from there (ORDER BY RowNumber, DataDate) but still comes out high fragmentation.


texts:
Let's say I was monkeying around on SQL Server and I wanted to reset everything to factory defaults, is that currently possible?

EXEC sp_configure 'show advanced options', 1
EXEC sp_configure 'stupid dangerous thingz', 1

I want to do something like

EXEC sp_factory_reset, 1


texts: SQL keyword difference between MySQL and Oracle 10g?

texts:
Good afternoon,

Are there differences between Oracle 10g and MySQL with the CREATE, ALTER, DROP, REF, PRIMARY KEY, FOREGIN KEY and attribute variable type keywords?

The reason I ask, is I'm planning to use MySQL WorkBench to generate the SQL for Oracle 10g. If you'd recommend a different ERD modelling tool, please suggest one that can be generated from text.

Thanks for all suggestions,

Alec Taylor


texts:
What does ADMINISTER DATABASE TRIGGER privilege actually mean?

I have the following case:

I have 2 databases: DB1 and DB2

On DB1 I have a trigger which arises after logon on database and checks if this username exists in some table. If not raises application error like "You are not permitted to log on!"

I created link on DB2, which is connecting to DB1 by some username, for example by kupa_dba.

Note that kupa_dba is not in an access list(in the table storing usernames that can connect to the database)

If kupa_dba doesn't have ADMINISTER DATABASE TRIGGER than trigger works, BUT if kupa_dba has ADMINISTER DATABASE TRIGGER  privilege there is no application error raising, trigger is ignored and DB2 can retrieve data from DB1


texts: How to count absence days from differents shift?

texts:
I have a problem when in the counting of the employee absences depending on different shifts, so all employees do not have the same shift, and what I should get is the group and the absences of this group ( the group contains some employees ),
Groups table
GroupId    GroupName
  1         Science
  2         Physics

Employee table
EmployeeId      GroupId
   001             1
   002             1
   003             2
   004             2
   004             2

Working_days table
DayId    DayName
  1       Monday
  2       Tuesday
  3        ...

Timetable table
TimeTableId   grouptimeId  WorkingDayId
    55eds          22           1
    e8d5s          33           2

GroupTime table
GroupTimeId    TimeTableId
     1           55eds
     2           e8d5s

How can I get absences for every group except the holidays?
select groupId,(case when groupId = null then 0 else COUNT(*) end) as absence from (select groupId,
COUNT(*) as  absences1 from
(select distinct MONTh,DAY,e.groupId
from employee_c c,holiday hl,employee e,groups,Get_Calendar_Date(DATEADD(MONTH, DATEDIFF(MONTH, 0, CAST(DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS datetime))+@Month-1, 0) ,DATEADD(MONTH, DATEDIFF(MONTH, -1, CAST(DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS datetime))+@Month-1, -1))
where DATENAME(DD, Weekday) IN (select WorkingdayId+1 from timetable s,groups ds,grouptime de where dd.groupId=ds.groupId and dd.groupId = de.groupId and s.timetableId=de.timetableId and de.groupId=ds.groupdId) and  DATEPART(MM,hl.startDate)=@Month and
c.isActive=1
except

(select Month,Day,d.departmentId from department d,Get_Calendar_Date(DATEADD(MONTH, DATEDIFF(MONTH, 0, CAST(DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS datetime))+@Month-1, 0) ,DATEADD(MONTH, DATEDIFF(MONTH, -1, CAST(DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS datetime))+@Month-1, -1)),holiday hle,employee_c c,employee e where datepart(MM,hle.startDate)=@Month and cast(Date as date) between hle.startDate and hle.endDate and c.isActive=1 and d.groupId =e.groupId and c.employeeId=e.employeeId and c.isActive=1 )) sc group by Month,Day,groupId) s group by groupId

Get_Calendar function:
ALTER FUNCTION [dbo].[Get_Calendar_Date]
(
    @StartDate DATETIME
,   @EndDate DATETIME
)
RETURNS TABLE
AS

RETURN
(
    SELECT  Tbl_Obj.RNo
        ,   DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate) AS [Date]
        ,   DATEPART(quarter,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [Quarter]
        ,   DATEPART(dayofyear,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [DayofYear]
        ,   DATEPART(WEEK,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [WeekofYear]
        ,   DATEPART(YEAR,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [Year]
        ,   DATEPART(MONTH,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [Month]
        ,   DATEPART(DAY,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [Day]
        ,   DATEPART(weekday,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [Weekday]
        ,   DATENAME(MONTH,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [MonthName]
        ,   DATENAME(weekday,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [WeekdayName]
        ,   (RIGHT( REPLICATE('0',(4)) +
                CONVERT([VARCHAR],DATEPART(YEAR,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)),0)
                ,(4)
             )+
             RIGHT( REPLICATE('0',(2)) +
                CONVERT([VARCHAR],DATEPART(MONTH,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)),0)
                ,(2)
             )
            ) AS [Vintage]

    FROM    ( SELECT ROW_NUMBER() OVER (ORDER BY [object_id]) AS [RNo]
              FROM sys.all_objects WITH (NOLOCK)
            ) Tbl_Obj

    WHERE   DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate) <= @EndDate
)


texts: Redirecting Report Server connections using host file

texts:
I am connected remotely to a server called New_Server and trying to connect to the Report Server which is installed on it by referring to another server:

I've changed the host file on NEW_SERVER to POINT to ITSELF when referring to another server: if I put in Old_Server name, it SHOULD point to itself; NEW_Server

So I put in this link: http://New_Server/ReportServer/ - it works
But then when I put in the link: http://OLD_SERVER/ReportServer/
then it asks me to RE-AUTHENTICATE, which I do, but it doens't seem to respond at all. It just seem to get my input and do nothing with it (since I am reprompted again and again for the username/password).

Issues:

Why should it even ask me to re-authenticate; should it NOT just replace "Old_Server" with "New_Server"? So shouldn't it just connect to
http://New_Server/ReportServer/ ??
Why does it ask for, then ignore authentication?

It's worth mentioning that I've blocked the OLD_SERVER using firewall. But again, this should not matter since I changed the host to refer to itself when it's trying to access OLD_Server

My research:

I've read this blog about changing URLROOT, and again this is not
    something I want to do or am trying to achieve. I am trying to fool
    the server to think it's connecting to OLD_SERVER but then
    referring it to NEW_SERVER
Using CNAME/DNS Alias is not an option
https://stackoverflow.com/questions/19447582/windows-8-ie-cannot-reach-sites-listed-in-hosts-file - I've done the troubleshooting steps from that question.
https://support.solarwinds.com/Success_Center/Network_Performance_Monitor_(NPM)/Knowledgebase_Articles/Edit_the_host_file_to_add_static_entry_for_hosts_that_are_not_resolving_by_DNS


texts: Obvious inefficiencies in this Netezza query?

texts:
Why might this query be taking nearly 2 hours to run when similar queries complete within a minute?

CREATE TABLE T3 AS
SELECT
        A.*,
        COALESCE(C.FIELD1,0) AS FIELD1A,
        COALESCE(B.FIELD2,0) AS FIELD2A,
        COALESCE(B.FIELD3,0) AS FIELD3A,
        COALESCE(C.FIELD4,0) AS FIELD4A,
        COALESCE(C.FIELD5,0) AS FIELD5A,
        B.KEY1,
        COALESCE(C.FIELD6,0) AS FIELD6A
FROM T1 A
LEFT JOIN T2 B
ON   A.KEY2 = B.KEY1
AND  B.TIMEFIELD1 <= DATE '28FEB2013'
AND  B.TIMEFIELD2 > DATE '28FEB2013'
AND  B.FIELD7= 'X'
LEFT JOIN T2 C
ON   A.KEY3 = C.KEY1
AND  C.TIMEFIELD1  <= DATE '28FEB2013'
AND  C.TIMEFIELD2 > DATE '28FEB2013'
AND  C.FIELD7= 'X'

DISTRIBUTE ON RANDOM

T1 has about a 400,000 rows; T2 has about a million rows. Both are distributed on random. T3 has the same row count as T1 (as expected).

EXPLAIN suggests the query plan uses nested loops.

(Apologies for the generic column names).


texts: InnoDB multicolumn fulltext search: SELECT MATCH requires single column index?

texts:
I have table with two fields and run a fulltext query with weighted order:

SELECT
  MATCH (`my_table`.`text_1`) AGAINST ('+lorem' IN BOOLEAN MODE) AS `reltext_1`,
  MATCH (`my_table`.`text_2`) AGAINST ('+lorem' IN BOOLEAN MODE) AS `reltext_2`,
  my_table.id
FROM
  my_table my_table
WHERE
  (
    MATCH (
      `my_table`.`text_1`,
      `my_table`.`text_2`
    ) AGAINST ('+lorem' IN BOOLEAN MODE)
  )
ORDER BY
  (2 * `reltext_1` + `reltext_2`) DESC

There's one multicolumn index containing both fields text_1 and text_2. This was a MyISAM table in the past and everything run fine.

Now when I switch the table over to InnoDB I see a Can't find FULLTEXT index matching the column list error. What I figured out is that everything works again if I add two additional single column indices for text_1 and text_2. Is this really necessary? Do I do something wrong?

Edit: Tested on MySQL 5.6.38


texts: Grant PG permissions to not existing tables

texts:
I would like to have a dedicated user in the schema, that can have access to tables/objects by pattern like foo* (starts with foo).

He should have permissions to create new tables like foo_table1 and perform insert/update on existing tables.

It is possible with MySQL, but not sure about PostgreSQL (easily, without stored procedures or other magic).

Is there a way to achieve this?


texts:
For the following query:

SELECT l.*
FROM `leads` AS `l`
INNER JOIN `users` AS `u` ON l.client_id = u.id
WHERE (`l`.`lender_id` = 1)
AND (l.claimed_date IS NULL)
AND (`l`.`disabled` = 0)
AND (u.last_activity_date > '2012-01-13 02:42:10')
AND (l.id = (
    SELECT `leads`.`id`
    FROM `leads`
    WHERE (lender_id = l.lender_id)
    AND (client_id = l.client_id)
    AND (disabled = 0)
    ORDER BY `price` DESC
    LIMIT 0, 1
))
GROUP BY `l`.`client_id`
ORDER BY `u`.`last_activity_date` DESC

How could we restructure the query to remove the correlated subquery?


texts:
All,

After several years of programming applications, I'm looking to refresh my understanding of databases. What I knew to begin with was fairly basic, and honestly, it's disappeared over time. I figured I could get an example and then come here to get a better understanding about the basics of relationships between tables.

Let's say I have the following three tables, describing train trips between three cities in the American Midwest.

The first is called TrainRide:

The second is called Customer:

The final table is called Journey:

I'm trying to figure out the primary keys, foreign keys, and relationships (one-to-many, many to many, etc.) of the tables. I've figured out that TrainRide's primary key is TrainNo. Customer's primary key is CustomerId. What I don't understand is the foreign key and primary keys of Journey, and I can't figure the _ to _ relationships between the tables.

I know this is more about the theory behind databases, but I figured this would be the place to ask.


texts:
In english, we might talk about the relation between, say, Bob and Tim. Perhaps they're cousins. The term "relation" in this context makes sense to me.

In the context of relational databases, I understand what the term refers to, but I don't understand why it is used. I figure that understanding why it is used will help me to better understand the field, so I'd like to understand why it is used.

Why is, for example, a Person, considered to be a "relation"? In english, a relation is a noun that describes how two entities are associated. It doesn't refer to the entities themselves. In the context of relational databases, "relation" refers to the entities themselves. Why?
I understand that relational model came after the hierarchical and network models (ex. parent, neighbor). But in those models, the entities also have relations to one another. So why call this model the relational model? Is there a more specific phrase/term? Or maybe we should say that all three models are relational models, but the hierarchical and network models are specific types of relational models?
What if we have standalone entities that don't relate to one another. Say, Person, Door, and Tree. Is the term "relation(al)" still applicable?

(Perhaps this should be multiple questions. I figured that the answers are highly related - perhaps there's just one answer - so I figured it'd make sense for this to be a single question. If I'm wrong, let me know and I'll create separate questions instead.)

Edit: This diagram may be useful to visualize that a relation is relating different domains to one another:


texts:
I have a database that is retrieving information quite slowly, I asked a question and was told to use full-text-indexing, this worked, but now we switched over to a .sdf file and Sql Server Compact Edition 4.0 does not support full-text-indexing

Is there any index that will mimic the way a full-text-index works?

Update and Insert Statement taking forever

This is the code I am using:

 SELECT TimesUsed, Word FROM Predictions WHERE (Word LIKE @partialWord) ORDER BY TimesUsed DESC

PredTable = PredTA.GetDataByPartialWord(combo)
        If PredTable.Count = 1 Then PredTA.Update(PredTable(0).TimesUsed + 1, combo)
        If PredTable.Count = 0 Then PredTA.Insert(combo, 1)


texts: SHRINK FILE works with SA but not as a normal user

texts:
My problem is that when I try to shrink the Log it works when I am logged in as SA but when I log in as a "normal" USER it does not shrink the log. (SQL Express 2008r2)

I have tried to find a answer to my problem but I cant. So please help me! =)


texts: Best way to perform backups with filegroups and then restore those backups

texts:
Scenario:

Database consists of these file groups:

PRIMARY
xxTEST2
xxTEST3
xxTEST4

Database consists of the following files in those file groups:

xxTEST_RESTORE - Rows Data - PRIMARY - xxTEST_RESTORE.mdf
xxTEST_RESTORE2 - Rows Data - xxTEST2 - xxTEST_RESTORE2.ndf
xxTEST_RESTORE3 - Rows Data - xxTEST3 - xxTEST_RESTORE3.ndf
xxTEST_RESTORE4 - Rows Data - xxTEST4 - xxTEST_RESTORE4.ndf
xxTEST_RESTORE_log - Log - N/A - xxTEST_RESTORE_log.ldf

2 tables are created with row:

dbo.COUNTRIES
dbo.CUSTOMERS

Then a full database backup is performed, and rows are added to the Customers table.

Question:

Want to add 1 record and have a backup of this
Want to add a 2nd record and have a backup of that
Want to roll back to the previous time when the 1st record was added (Q #1 above)

Should I perform a TRANS LOG backup after adding the 1st record or a FileGroup backup?
What order of backups should I perform to ensure I can roll back properly as I keep getting errors that either the database is in restore mode, doesn't like the trans log I pick, etc....

Looking for the proper sequence for backup and then restore


texts: Migrating MySQL 8.0.20 database to AWS Aurora

texts:
I originally posted this question on stack overflow as I did not know this service existed and someone suggested I may get a better answer here.
I was trying to use the AWS RDS Proxy service and realized that the only compatible versions with the service are AWS Aurora databases/clusters.
The current database I have is a MySQL database using MySQL version 8.0.20. AWS Aurora is MySQL compatible but only with version 5.6 and 5.7. I am not overly familiar with these versions and how different it would be to version 8.0.20.
Has anyone gone through the process of converting a MySQL database to Aurora? I know there are docs on the subject out there but they deal with version 5.6 or 5.7, has anyone done it for later versions?
I have all the MySQL code in SQL files along with the stored procedures so the database creation can be automated to an extent, it would just be if anyone has ran into issues doing it/found a better way than running multiple SQL files to transfer over data.
If you have gone through the process are there major differences between MySQL and Aurora in terms of running stored procedures etc?
EDIT
Just to give a bit of background, this question is the original reason I am migrating to Aurora as it seems to have a better service for managing connection pools https://stackoverflow.com/questions/67600414/should-i-use-connection-pooling-in-aws-lambda


texts: MySQL- Can Dump Table From WorkBench But Not CLI

texts:
I have a table 'RawData' that which has a number of BLObs that hold a significant number of readings. Usually I have used MySQL-WorkBench (5.2.38+dsfg-3) but thought would go over to the Command Line Interface (CLI). However, whereas WorkBench has no problem the 'mysqldump' fails with:-

Error 220: Got packet bigger than 'max_allowed_packet' bytes when dumping table RawData at row:213.

The exact command used was:-

mysqldump -u root -p HealthData RawData > rawdata.sql

mysql is Version 5.5.28-0ubuntu0.12 running under Mint 17.3


texts:
I am asking two questions in this post. If I see activity in netstat with port 1433 for an unnamed instance, whose tcp addresses are not enabled, what does that mean?

What is the preferred way to configure a dynamic port that occurs with my second, named instance? Here are the details:

This post is slightly different than my first post, because in the last post I overlooked a critical detail with the default, unnamed instance, which was I could find no evidence that any of the TCP addresses were enabled. Here is some background:
I have a Windows 2016 server with an unnamed and a named MS SQL SERVER instances installed. TCP and Memory connections are turned on for both, until you get to the protocol section for TCP.
Both instances use the same IP address. The unnamed instance uses port 1433; and the named instance uses 49365.
The unnamed instance was installed first, and hence was assigned port 1433. The firewalls on the server and the client (Windows firewall) are set to allow the necessary UDP and TCP traffic.
Here are two pictures denoting most of the port configurations for the unnamed instance. Please note enabled is set to No for all settings, there is no dynamic port, and the port used is 1433.

This database belongs to a vendor, whose clients are able to connect to the database, so I am trying to figure out that happens in the hope that information will help me to connect to the named instance.
I've tried all suggestions in the prior post, read on-line, and my named database was able to do an import from an older SQL Server database, 2008.
Using netstat, I can see port 1433 is in use, so there is no point in co-opting it for the named instance. Should I just pick a port I know won't be used, or is not likely to be used?


texts: postgres; do indexes automatically reindex new data?

texts:
If I have a bunch of rows in a Postgres table, then create an index, does the index get automatically updated if new data rows are inserted after it's creation?

Alternatively stated, is REINDEX needed only in some rarer scenarios, or whenever new data is added?


texts:
I have the following tables in my database:
1.Roomtypes
2.Reservations

Roomtypes have the following columns
id,
product_id,
type,
total_no_of_rooms,
created_at,
updated_at

Reservation has the following columns
id,
roomtype_id,
customer_id,
check_in,
check_out,
no_of_rooms,
created_at,
updated_at,

i need to get the rooms remaining in roomtype table if someone books a room. that is details no of rooms in a specific date,rooms which are not in reservations. can anyun help me with the query


texts:
I am trying to create a database for a small book library. This is not a professional project, just an exercise of my own. This is my first time to try this ( I am self-taught ) and I am encountering a problem. Before I continue I will provide my version of the ER diagram below ( I made it in Paint, using the following resource for notation ):

My biggest problem is to solve the fact that book can have the same title but have several editions or be a part of a set. An example would be Lord of the Rings trilogy, or Programming Windows, 5th edition, or Encyclopaedia of chess openings, volume D.

I don't know how to incorporate this into my ER diagram so that I can avoid data redundancy.

Therefore I ask these questions:

How should I address the fact that book can have the same title but have several editions or be a part of a set, in my ER diagram?
Since I am inexperienced, can you point out mistakes in my ER diagram, if there are any, and suggest an improvement?

Since this is my very first post here, please leave a comment if anything needs to be changed ( if it doesn't fit this site's question format ) or if you need further info.

Thank you.


texts: connection to remote Analysis Services from SSMS

texts:
On my company local SQL Server instance I was able to add a linked server to a remote AS instance (in Management Studio > Server Objects). I was also able to connect using Data Tools, as well with Excel. In these clients I am able to run custom DMX queries

The issues that I face are:
- In SSMS linked server, the explorer shows only dimension tables, no measures.
- Same in Visual Studio Data Tools (no measures are displayed in the explorer UI), except that here I also see "columns" for each table (while in SSMS tables don't show any lower level object to explore).

While in Excel OLAP pivot table I see all dimensions and measures, I therefore have access to all the cube's objects - or at least the ones allowed for the login used in the connection string.

For all of the above mentioned connections, I successfully used the same string, which is something like:

Provider=MSOLAP.6;Data Source=biserver.domain.com:1564;Persist Security Info=True;
User ID=myusername;Password=myPassword;Initial Catalog="MyCatalogue Name";
Location=biserver.domain.com:1564;MDX Compatibility=1;Safety Options=2;
MDX Missing Member Mode=Error

The remote AS is located out of my company domain. I have no additional info on how the instance is configured, nor possibility of having more permissions granted on the login, nor establish a VPN access.

Question is: if Excel lets me see the data I need (measures and dimensions with their hierarchies), why am I unable to see the same with other clients, using the same connection string?

Best would be if I could connect directly with SSMS (not using the linked server), by selecting "Analysis Services" server type on the initial prompt.
Of course I tried, but given that only Windows Authentication is allowed there, even if I pass the entire string on the "Additional Connection Parameters" tab, I always receive the following error:

A connection cannot be made. Ensure that the server is running.
(Microsoft.AnalysisServices.AdomdClient).
No such host is known (System)

I also tried to create a local Windows login with same user and pw and open SSMS as that login, but of course that didn't work.

Any help is really appreciated.

Don't know if this can help: we have no local AS instances running on our servers. SQL Server Browser is running

Thanks


texts: how login other account by sysdba in oracle?

texts:
I forget hr's password, but I have sysdba role.

Can I use su hr like in linux.

I don't hope alter hr's password.


texts: SQL Server log file used pecentage does not drop after multiple backups

texts:
SQL Server 2005

I ran "dbcc SQLPerf(logspace)", then took two log backups, and ran "dbcc SQLPerf(logspace)" again. No change from 48.55% used, even though "dbcc opentran (foo)" says that there are no active open transactions.

Does that mean that there are inactive open transactions that are preventing the log free space from changing after backups?

Thanks.


texts: How can I duplicate a row and change a single value, without listing all column names?

texts:
I'm working on a legacy system, so I can't change the architecture of this. The system has the concept of templates. These are used to accompany invitations, format reports, and other stuff like this. A choice was made a couple of decades ago that all the different template types should have their own table. So now I have 1 table with the template name, id and other metadata, and 25 tables with different contents, all with their own schema. They all have two fields in common, ID and TemplateID, the first is a identity field that is never used for anything, the second references the table with name and metadata.

The owner has decided they want to be able to clone templates. They often use mostly the same template for several years in a row. So what I'm after is a generic solution to - for 25 separate tables - duplicate a row, and change the TemplateID of the duplicated row to a new ID corresponding to a new entry in the metadata table. I have found a couple of clone-row solutions, but they all seem to expect that the ID or field you want to change is the primary key/identity column. And I could of course write 25 different insert into <table> select col1, col2, col3, col4, <new TemplateID>, col6, col7 statements but that irks me in ways I can't explain.

Anyone have a more elegant solution to the problem? I'm running sql-server, and I'm currently stuck in 2008 compatibility mode :(, nothing I can do with that for now.


texts: Why would SQL cast have to be expensive if used on a join column?

texts:
I see that sql join internally functions always on some of the three basic operations : nested loops join, merge join and hash join.

From multiple articles on the internet I see SQL casting is said to be able to impact performances severely and almost always the reason is it disables index usage.

But to me it looks it could always be avoided:
Casting could disable merge join usage (and merge benefits from index usage) but hash join, that is said to be generally faster than merge, can still be utilized in the same way(looks to me). No matter in which phase of the hash join (build of probe) casting is applied, all that has to be additionally done is casting, before hashing, for the column casted.

Hash join, anyway, passes every row of the first table once, same as for the second table.
Hence, from my aspect, casting of join columns, applied to hash join, has not performance impact but the casting operation itself. And the guys on the internet say casting severely impacts performances since indexes cannot be used?

Is something wrong in my thinking above?
Thank you for the time


texts: What is the new JDBC 4.3 Connection Request feature with `beginRequest` & `endRequest` methods?

texts:
The JDBC 4.3 update arrived with Java 9 2017-10. Amongst its few changes is the new feature on Connection where the beginRequest and endRequest methods signals…

  Hints to the driver that a request, an independent unit of work, is beginning on this connection. Each request is independent of all other requests with regard to state local to the connection either on the client or the server. Work done between beginRequest, endRequest pairs does not depend on any other work done on the connection either as part of another request or outside of any request. A request may include multiple transactions. There may be dependencies on committed database state as that is not local to the connection.

…and…

  … is optional, vendor specific and should largely be transparent.

➠ Please explain the purpose of this feature.

It seems to be about something larger than transactions yet is not about two-phase commits. Is it related to sharding, the major new feature in JDBC 4.3?

Is it some kind of scope that wraps around multiple transactions?
What databases make use of this feature?

In particular, does Postgres, H2, or or Microsoft SQL Server have such a feature?

I am not asking about whether these databases have JDBC drivers updated yet for JDBC 4.3 yet. I am asking about the bigger picture, what is the meaning and purpose of a "request" in the context of a database connection across connections.

Oddly, the JDBC 4.3 specification makes only this one mention, with no explanation:

  JDBC API changes

  The following changes were made to existing JDBC interfaces..

  java.sql.Connection

  Added the methods beginRequest,endRequest, setShardingKey,
  setShardingKeyIfValid.


texts: Weird deadlock error after switching from MyISAM to InnoDB

texts:
I have been told many times that I should switch my MySql tables engine from MyIsam to InnoDB. Finally I did that, only to have to urgently switch it back to MyIsam this morning. Some PHP scripts break on a weird error that is quite beyond my understanding:

17:01:42 mySQL error occured: Deadlock found when trying to get lock; try restarting transaction
 UPDATE s
 SET combined = '2037-01-01 00:00:00'
 WHERE combined <= 0
 ORDER BY combined ASC
 LIMIT 10000

BTW, I've got an AFTER UPDATE trigger for table s:

CREATE TRIGGER a
AFTER UPDATE ON s
FOR EACH ROW
BEGIN
    SET @lower_tolerance_euro := 4;
    SET @upper_tolerance_euro := 300;

    IF (ABS(OLD.price - NEW.price) >= @lower_tolerance_euro AND
        ABS(OLD.price - NEW.price) <= @upper_tolerance_euro AND
        NEW.passengers = 1 AND
        NEW.validon > OLD.validon)
    THEN
        INSERT IGNORE INTO history
            VALUES (NEW.fkey, NEW.validon, ROUND(NEW.price));
    END IF;
END $$

There are two more triggers on the same table: AFTER INSERT and AFTER DELETE. Everything worked fine before. Table history has no any triggers! Any clues how to debug this? There is no any deadlock really af far as I understand...


texts: how to fetch different data on compare rows of two tables


texts: Which AWS RDS; current t3.medium over CPU baseline, but not sure what's the next best option

texts:
I currently have one RDS instance, db.t3.medium running Aurora MySQL.
Due to increase workload (expected), the CPU is, on average running just over its baseline and therefore in about 10 days will run out of CPU credits and then start getting expensive. The 15-minute average CPU Utilization is around 22% (baseline is 20%).
RDS doesn't support any larger db.t3. versions for Aurora MySQL so I either need to change instance class, or move to Aurora Serverless, or look at a second instance alongside. I'm not sure what's going to be the best move so keen to get some advice.
Second Instance
Up until now I've avoided running a second db instance because:

I don't fully understand how to set it up correctly to share the workload (adding different endpoints for read vs write in my applications would be tricky)
I'm not familiar if this would actually share the load like an EC2 load balancer does
most of my MySQL CPU usage is from reading (relatively little write), so I don't predict splitting it to a reader and a writer helping too much (the reader will probably still run high). But my understanding around how this all works is patchy.

I'm aware of the pitfalls of a single instance around availability/fallover etc so it would be nice to mitigate this as a bonus from this upgrade.
Switch Instance Type
If I switch instance types, a db.r5.large is twice the cost of a db.t3.medium but still only has 2 x vCPU. It has a lot more Memory (16 v 4) but my issue is CPU.
I'm not sure how the ECU (the actual relative measure of processing power) compares on db.r5.large vs db.t3.medium as it's documented as 'variable', so not sure if I'm comparing apples with oranges between the r5 and the t3.

db.r5.large ECU = 10
db.t3.medium ECU = Varaible

The db.r5.xlarge has 4 vCPU but would quadruple my RDS costs.
Sidenote:
I have 9 months left on a t3.medium reserved instance, so would lose that if I do anything other than setup a second t3 instance.
Serverless
Based on the fairly vague documentation around serverless resources (1 ACU has approximately 2 GB of memory with corresponding CPU and networking, similar to what is used in Aurora user-provisioned instances.), I would guestimate 2 ACUs is the equivalent of a db.t3.medium, so 4 ACUs would give me double the CPU as my current setup, for roughly the same cost as a db.r5.large.
Serverless would throw in the added benefit of built-in availability and replication.
The RDS load is fairly steady, rather than spiky, so I wouldn't benefit from any time the Serverless scales down to 1 ACU for example.
What's best?
What would you do, and why?!
Thanks!
Additional Info

Any SSD or NVME devices on MySQL Host server? Not sure - it's just a standard AWS RDS instance but not sure what default devices are used.
SHOW GLOBAL STATUS; https://pastebin.com/xkTaPKKF
SHOW GLOBAL VARIABLES; https://pastebin.com/CGjeGJCv
SHOW FULL PROCESSLIST; https://pastebin.com/T8YveG5q
SHOW ENGINE INNODB STATUS; https://pastebin.com/EkQpyqui
SELECT name, count FROM information_schema.innodb_metrics ORDER BY name; https://pastebin.com/kemMrNpS
MySQl Error Log - last 12 hours - all of which were generated during a restart of the server: https://pastebin.com/A6sV0Qxc


texts: SSISDB user "AllSchemaOwner" is not SQL Server Login

texts:
Did some research in SSISDB and found a user AllSchemaOwner which is not associated with any SQL Server login:

SELECT p.name, p.type_desc
FROM SSISDB.sys.database_principals as p
LEFT JOIN sys.sql_logins as l ON p.sid = l.sid
WHERE p.principal_id > 4 and p.type = 'S' and l.sid is Null;

However, there are plenty of objects in the database, which were created with credentials of that account.

I've tried to run one of these procedures and it was executed successfully.
When I've tried to recreate that scenario in my test database it returned me an error:

USE [master]
GO
CREATE LOGIN [Test2] WITH PASSWORD=N'test', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [MyTestDB]
GO
CREATE USER [test3] FOR LOGIN [test2]
GO
SELECT 1 as test INTO tbl_Test;
GO
GRANT SELECT ON tbl_Test TO [test3]
GO
CREATE PROCEDURE sp_Select_TEST
WITH EXECUTE AS 'Test3'
AS SELECT * FROM tbl_Test;
GO
EXECUTE sp_Select_TEST;
GO
DROP LOGIN [test2];
GO
EXECUTE sp_Select_TEST;
GO
DROP PROCEDURE sp_Select_TEST
GO
DROP TABLE tbl_Test
GO
DROP USER [test3];

Msg 15517, Level 16, State 1, Procedure sp_Select_TEST, Line 0 [Batch Start Line 29]
Cannot execute as the database principal because the principal "test3" does not exist, this type of principal cannot be impersonated, or you do not have permission.

So, the question is: How it is possible that procedure created with an option WITH EXECUTE AS 'AllSchemaOwner' can be successfully executed while SQL Server login does not exist for that user?


texts:
Need your help optimising an insert query.

I have two tables in my MySQL DB. I will be inserting into Table1. Information will be retreived in Table2.

Table 2 contains a couple of million records.
My insert query should look for all users who has a previous event in Table2 and insert it into Table1.

This is my insert query - but it's killing my server.

INSERT INTO Table1(
SELECT
    d.created,
    d.userID,
    d.eventID,
    d.previousEvent
FROM (
SELECT
    e.created,
    e.userID,
    e.eventID,
    (SELECT eventID FROM TABLE2 pe WHERE e.userID=pe.userID AND e.created>pe.created ORDER BY pe.created DESC LIMIT 1) AS e.previousEvent
FROM
    TABLE2 e
WHERE
    e.eventID not in (Select f.EventID from TABLE1 f)
ORDER BY
    e.created desc
) d
WHERE
    d.preEvent is not null
GROUP by
    d.eventID,
    d.created,
    d.userID
);

eg:

Table1:

created     |   userID    |   eventID(unique)   |   previousEvent
2015-11-15      66666          1045698664           4566660001

Table2:

created     |   userID    |   eventID(unique - random)
2015-11-15      55555          4755500001
2015-11-15      66666          4566660001
2015-11-15      77777          8634757777
2015-11-15      88888          1845562565
2015-11-15      66666          1045698664


texts: Populate Identity column with sequential numbers restarting count on change of key field

texts:
I am attempting to populate an Identity column with sequential numbers but I want the count to start over when the value of a key field changes.

Example data and desired ID column results:

Month_  Date_   Year_   SalesRep  ID
10  2018-10-31  2018    AL01      0
10  2018-10-31  2018    AL01      1
10  2018-10-31  2018    AL01      2
10  2018-10-31  2018    AL01      3
10  2018-10-31  2018    AL02      0
10  2018-10-31  2018    AL02      1
10  2018-10-31  2018    AL02      2
10  2018-10-31  2018    AL02      3

The below query does not produce the desired results:

INSERT INTO SALES_INFO_SNAPSHOT_WD_TEST(ID)
SELECT DEL_ID
FROM (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY Year_,Month_,Date_,SalesRep ORDER BY
SalesRep) - 1 As DEL_ID

FROM
Sales_Info_Snapshot_WD_TEST
where date_ = '10/31/2018') D
WHERE DEL_ID IS NOT NULL

Anyone know of a painless solution?

The select query works perfectly but does not insert into the table.
I need to number these rows in order to identify a large chunk of data that needs to be deleted from a very important reporting table.

Thank you.  Any help is appreciated.

Marlene
SSMS v 17.7


texts: Linked servers not ignoring case after reboot

texts:
We have a SQL Server 2014 database with collation SQL_Latin1_General_CP1_CI_AS, which is case insensitive.  It is linked to another SQL server database with the same collation.  The second database was rebooted last week, and since that time, comparisons in the first database to the second are case sensitive.

For instance, code in the first will have something like:

select *
from [LINK-SERVER].schema.dbo.table tbl
where tbl.fldname = 'tagName.TagValue'

That used to work, even though the data on the linked server had the value 'tagName.tagValue'.  After the reboot, that uppercase 'TagValue' doesn't match 'tagValue' and no data is found.

Since this type of code is used in several places (and I've fixed some of them), and I'm still searching for where all it is used - a better solution would be to remind the databases that case is supposed to be ignored.

What is likely the problem, and how do we get them to ignore the case again?

(I am not the DBA, so I'll have to ask for something to be done.  I asked for the DBA to reset the link, but either the wording I'm using isn't right, or else that's not what needs to be done.)

Update
From the queries in the comments, the first query returns is_collation_compatible = 0, and uses_remote_collation = 1.

The query on name, collation name from sys.columns and sys.tables: it returns SQL_Latin1_General_CP1_CI_AS, but I'm not finding the table explicitly, which makes me wonder if the table name is a synonym.

The query on name, collation_name from sys.databases returns SQL_Latin1_General_CP1_CI_AS for the database.

I have limited access in the database I can get to, and in the linked server properties for the LINK-SERVER, Use Remote Collation is set to true.

And I see that the linked server is SQL Server 2008 SP1 (10.1.2531.1 Intel X86) and I do NOT have 2014, but rather 2012 (SP2-GDR) (11.0.5388.0 X64).  I will update my tags.

Update
EXEC(N'SELECT SERVERPROPERTY(''collation'');') AT [LINK-SERVER]
returns SQL_Latin1_General_CP1_CI_AS

Running the second query with COLLATE SQL_Latin1_General_CP1_CI_AS at the end returns data, but only if the capitalization is correct.  If it is not, nothing is returned.  I'm guessing this query is to force this collation.  It refuses to be moved.  At this point, I'm not sure what to do but to consider case important and modify my code.


texts: Oracle 11.2 index on VARCHAR vs index on DATE

texts:
I have a table with 15,339,375 records and it has an index on a date field that's converted to a VARCHAR(5) and stored as "MMYDD".  It has an index on the field.  The records in the table are all from 2015.

When I run a count(fieldname) against the table for all records BETWEEN '01501' AND '12531' it returns15,339,375 in 92 seconds.

Now I copied this table, converted the date field to an actual date, and created an index on the field.  When I run the same query for the range BETWEEN TO_DATE('01/01/2015', 'MM/DD/YYYY') AND TO_DATE('12/31/2015', 'MM/DD/YYYY') the query never completes and I have to kill it after an hour or so.  Running the query for just January and February takes 88 seconds the first time and 2.9 seconds the second time.

Now why would an index on a VARCHAR(5) field be more efficient than an index on a DATE field?  Also why would the query run faster the second time?


texts: triggers - using the inserted / deleted tables in Dynamic SQL

texts:
Within a trigger, I'm trying to create a unique table name (using the NEWID()) which I can store the data that is found in the inserted and deleted tables.

Declare @NewID varchar(50) = Replace(convert(Varchar(50),NEWID()),'-','')
Declare @SQLStr varchar(8000)
Set @SQLStr= 'Select * into [TMPIns' + @newID + '] from inserted'
Exec (@SQLStr)

I get the following error: Invalid object name 'inserted'

I know I can do:

Select * into #inserted from inserted
Set @SQLStr= 'Select * into [TMPIns' + @newID + '] from #inserted'
Exec (@SQLStr)

But I don't want to use TempDB as these tables can become big and I also feel that it is redundant. Is there a way to avoid the creation of #inserted?


texts:
i'm trying to do a command that gather stats of tables that has more than 10 million rows. Right now only one table has, but i want the command to execute the gathering to more tables as soon as they reach 10 million rows.

Right now I have this command, but i can't find out what is wrong that is preventing it of running ok. I have ~ok~ sql skills, but almost zero experience with Oracle PL SQL.

Can someone point me a direction? Tks in advance.

DECLARE
 CURSOR t1 is
     select table_name from dba_tables where owner='SAPIENS_TESTE' and num_rows > 10000000;
BEGIN
 OPEN t1
 FOR t IN t1 LOOP
    EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SAPIENS_TESTE', TABNAME => '||t.table_name||', partname => NULL, granularity => 'GLOBAL', CASCADE => TRUE, method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO');
 END LOOP;
END


texts: Postgres 10 Log Shipped Replica with cascading replication

texts:
I'm planning for a tricky (at least for me) migration whereby I have master postgres 10 db from a cloud provider that will not allow for replication user connection. I do have a base backup and WAL getting shipped to AWS S3. I've easily set up a log shipped replica as a hot standby to promote and officially migrate off initial cloud platform.
However, I'd also like to have another warm stand by ready and available immediately for HA after the initial log shipped replica is promoted in the event of a failure I have something immediately available.  There are several TB of data so it takes a few hours to generate a replica representing significant risk after database promotion.


texts: What cases benefit from the Reduce, Replicate, and Redistribute join hints?

texts:
The From Clause Documentation starting with SQL Server 2008 briefly mention 3 join hints and their basic mechanisms:

Reduce
Replicate
Redistribute

However there does not seem to be much information on when it might become necessary to use them.

It appears that they can be used in conjunction with the hash, loop and merge which are already understood for purpose of this question.

The relevant section from the documentation:

  For SQL Data Warehouse and Parallel Data Warehouse, these join hints apply to INNER joins on two distribution incompatible columns. They can improve query performance by restricting the amount of data movement that occurs during query processing. The allowable join hints for SQL Data Warehouse and Parallel Data Warehouse are as follows:

  REDUCE
  Reduces the number of rows to be moved for the table on the right side of the join in order to make two distribution incompatible tables compatible. The REDUCE hint is also called a semi-join hint.
  REPLICATE
  Causes the values in the joining column from the table on the left side of the join to be replicated to all nodes. The table on the right is joined to the replicated version of those columns.
  REDISTRIBUTE
  Forces two data sources to be distributed on columns specified in the JOIN clause. For a distributed table, Parallel Data Warehouse will perform a shuffle move. For a replicated table, Parallel Data Warehouse will perform a trim move. To understand these move types, see the "DMS Query Plan Operations" section in the "Understanding Query Plans" topic in the Parallel Data Warehouse product documentation. This hint can improve performance when the query plan is using a broadcast move to resolve a distribution incompatible join.


texts: Having Updatable Views that obey initial select conditions

texts:
Let's say I have a table like this:
+--------------+----------+-------------+
| name         | is       | in          |
|--------------+----------+-------------|
| Study        | <null>   | <null>      |
| Sport        | <null>   | <null>      |
| Biology      | Study    | <null>      |
| Linguistics  | Study    | <null>      |
| Art          | <null>   | <null>      |
+--------------+----------+-------------+

And I want to build a view as such:
CREATE VIEW v AS SELECT name FROM t WHERE "is" = 'Study';

Now if I want to insert into this view, it does not automatically set "is" to 'Study', I have to write a trigger and a function to adjust this behavior. However, this is a highly repeated pattern in my application, and I was wondering if there is a way to automatically achieve this (an updatable view that obeys the select condition).


texts: SELECT INTO that won't stop on SQL Server

texts:
I ran a query a number of hours ago with a select into statement. I canceled the query, but it took awhile and my SQL Server client froze. The database that query was running on is now locked.

When I run sp_who2, I see these statistics:

Status: Runnable
BlkBy: "." <-- meaning blocked by nothing?
Command: Select into
CPU Time : 0
Disk IO : 0
LastBatch : 12/30/2016 (today)

Is it that the query is still taking time to close? The status runnable apparently means its waiting for something -- is it waiting for more space allocation? I do not have sufficient privileges to kill the process and no one is around to help.

Could someone assist with this?


texts: Using an index when leading column is not in the predicate

texts:
Are there any circumstances when an index could be used when not all the index columns are included in the predicate?  In my case the leading column in the index is unique and is part of the unique primary key so I am wondering if SQL can still use the index, as it strikes me it has all the information to know its unique.
e.g.
CREATE TABLE x (a int, b int, c int, CONSTRAINT y PRIMARY KEY CLUSTERED (a ASC, b ASC))

SELECT * FROM x WHERE b = 1

This does not use the clustered index (it uses some other NC index).  Of course if I specify the leading column it does.
 SELECT * FROM x WHERE a = 1 AND b = 1


texts: Migrating BLOB in Oracle into PostgreSQL

texts:
I use the data type BLOB in an Oracle table to store images and document files.  Now for Postgres(9.4.5), I have two options, i.e., BYTEA or OID.

With consideration of passing the params (SAVING) from the Java side as follows:

DiskFileItemDeepy file  =  myFile;
InputStream is = null;
long fileSize = 0;
if (file != null && file.getFileSize() > 0){
  is = file.getInputStream();
  fileSize = file.getFileSize();
  call.setBinaryStream(1, (InputStream)is,  (long)fileSize);
}
...
call.execute();

//When retrieve the data use:

java.sql.Blob blob = (Blob) resultSet.getBlob(tableColumnName);

For the purpose mentioned above, which Postgres data type is a better candidate for replacement of the BLOB, is it BYTEA or OID?