texts: SQL2012 - Send backup to client via SQL Job

texts:
Something I thought would be fairly easy..

We host a website for a client. They would like us to send them a database backup monthly. They have Email limits that make sending via EMail not viable. They don't have an FTP site.

I'm trying to come up with a fairly simple, reliable way to (from a scheduled SQL job) copy a backup file to a (free) cloud account like GoogleDrive or OneDrive or ?? that the client can easily access.

No problem creating a SQL job for the backup, scheduling it, etc. the problem is how to then copy the file to an internet destination with minimum hassle. The jobs run under the default SQL Service account.

I'm open to suggestions..?


texts: How to safely eject a tablespace oracle and move it to another machine

texts: I've a tablespace created on a external hardisk with one big datafile. If there are no connections to it, is it safe to eject the disk and plug it into a new machine? And how to map the tablespace to the oracle in the new machine?


texts:
hello everyone i try to find minimum and maximum value of SUM(units_sold) i test some ways but i can't find best solution please help me !

    SELECT TOP 1    dp.product_name, SUM(fs.units_sold) AS SumOfunits_sold
    FROM           dim_product dp
    INNER JOIN  fact_sales fs
        ON     dp.product_code = fs.product_code
    GROUP BY
            dp.product_name
    ORDER BY
        SUM(fe.units_sold) DESC
UNION ALL
     SELECT TOP 1     dp.product_name, SUM(fs.units_sold) AS SumOfunits_sold
      FROM   dim_product dp
      INNER JOIN  fact_sales fs
      ON     dp.product_code = fs.product_code
      GROUP BY
             dp.product_name
      ORDER BY
                SUM(fe.units_sold) ACS ;


texts: How to Identify the user who issued MySQL Shutdown command

texts: We have had several situations when our production MySQL server suddenly became unavailable.  The error log just shows "Normal shutdown" followed by the typical shutdown messages.  How can I determine the Linux or MySQL user account and host name of the connection issuing the shutdown command?


texts: Unable to change database owner to a user that is already mapped as user

texts:
I ran into this problem.  Trying to change a database owner to acct1; however, I get the following error message when doing so:

  The proposed new database owner is already a user in the database

Now I know one solution is to drop the acct1 user and then change the database owner, but I can't do that because all the tables in this database are owned by the acct1 schema.

How can I get around this?


texts: delete records in one table based on multiple column values in second table

texts:
I need to insert and delete records in table 1 based on the data in multiple columns in table 2.

Example:  Table1 and Table2 have identical columns: address, city, state, and zip.

Table1 is the master table with millions and millions of records.
Table2 (thousands of records) is a table based on data that changed in some way - although it may not be a change in data in this table.

Matching records means that the address, city, state, and zip are identical in value in both tables.

So I want to do 2 operations:

Insert records into Table1 that only exist in Table2
Delete records from Table1 where the record is not in Table2 but the city
is in Table2

Based on the size of Table1, I need a very efficient way to execute this.
I know some SQL - but not to the level this would require if done with SQL.

This is a DB2 database - version 10.5.


texts:
I'm trying to select the time field from five tables and return the oldest time. The userid field in every table is indexed. Here's the query:

(
SELECT  `time`
FROM  `eventlog`
WHERE userid =  '9819'
)
UNION ALL (

SELECT  `time`
FROM  `screenshots`
WHERE userid =  '9819'
)
UNION ALL (

SELECT  `time`
FROM  `sitelog`
WHERE userid =  '9819'
)
UNION ALL (

SELECT  `time`
FROM  `keylog`
WHERE userid =  '9819'
)
UNION ALL (

SELECT  `time`
FROM  `webcamcaps`
WHERE userid =  '9819'
)
ORDER BY  `time` ASC
LIMIT 1;

Here's the EXPLAIN:

+----+--------------+------------------+------+---------------+--------+---------+-------+------+----------------+
| id | select_type  | table            | type | possible_keys | key    | key_len | ref   | rows | Extra          |
+----+--------------+------------------+------+---------------+--------+---------+-------+------+----------------+
|  1 | PRIMARY      | eventlog         | ref  | userid        | userid | 4       | const |  308 |                |
|  2 | UNION        | screenshots      | ref  | userid        | userid | 4       | const | 7850 |                |
|  3 | UNION        | sitelog          | ref  | userid        | userid | 4       | const | 1949 |                |
|  4 | UNION        | keylog           | ref  | userid        | userid | 4       | const | 3589 |                |
|  5 | UNION        | webcamcaps       | ref  | userid        | userid | 4       | const | 8035 |                |
| NULL | UNION RESULT | <union1,2,3,4,5> | ALL  | NULL          | NULL   | NULL    | NULL  | NULL | Using filesort |
+----+--------------+------------------+------+---------------+--------+---------+-------+------+----------------+
6 rows in set (0.00 sec)

How can I optimize this so I'm not looking through so many rows?


texts: How to manipulate the query plan so that I can have more control on memory grants?

texts:
Let's say I have a complex query and for this query there are huge
Differences between the estimated and actual execution plans.

These differences are causing spillage into tempDb as described here.

The problem is that in this query there are tables that are too big for me to update the statistics.

How can I manipulate the query plan so that I can have more control on memory grants?

I want to avoid memory spillage to tempdb.

In order for this question to be objective and not too broad, we could concentrate for instance on the hash join operator, when can I replace it by a merge join operator?

Are there other operators with memory grant that could be replaced depending on the work load?

Understanding SQL server memory grant

-- Search cache for queries with memory grants:
SELECT t.text, cp.objtype,qp.query_plan
FROM sys.dm_exec_cached_plans AS cp
JOIN sys.dm_exec_query_stats AS qs ON cp.plan_handle = qs.plan_handle
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
WHERE qp.query_plan.exist('declare namespace n=''http://schemas.microsoft.com/sqlserver/2004/07/showplan''; //n:MemoryFractions') = 1


texts: SQL Server 2005 Datawarehouse DB - Integrity failure caused by NonClustered indexes

texts:
Last year I've been working with a data warehouse database (SQL Server 2005) loaded on a daily basis during the nights. Overall database performance is acceptable. However, there is a "known error" that appear with no time-pattern that makes the db integrity fail and the process crash with all the logic consequences: manual work, reporting delay, users complaints, etc.

The error:

  Msg 8952, Level 16, State 1, Line 1
  Table error: table 'Table_Name' (ID 99999999). Index row in index 'Index_Name_A' (ID 2) does not match any data row. Possible extra or invalid keys for:

  Msg 8956, Level 16, State 1, Line 1
  Index row (1:24482:16) with values (LastName = 'Smith' and CustomerID = 22) pointing to the data row identified by (CustomerID = 22).

Details:

Index_Name_A is a nonclustered index
The table hosting the index is a fact table where 4 million rows or above are deleted/inserted on daily
The code that load the table has already in place a DISABLE/REBUILD INDEX process right before/after the action. However the problem is still there

Things I already tried:

Validated SEVERAL times targeted values to load are exactly the same datatype as in the destiny column with the exclusion of NULL values.
DROP/CREATE indexes rule instead of DISABLE/REBUILD. There were not improvements.
This only happens over NONCLUSTERED INDEXES of this specific table. CLUSTERED INDEXES of the same table or other NONCLUSTERED INDEXES of other fact tables does not have issues.
Under the assumption it would be an error on the physical pages where the table was stored, a new table was created, data exported to it and original table deleted. That was not the solution either.
DBCC CHECKTABLE is part of the current corrective actions with big costs: it needs to be executed under single user access during business hours. This is definitely the only way I have found to solve the problem but I need to keep affecting the business.

Question:

The question is pretty simple. Is there any way I can fix this error in a preventive manner? I don't want to keep doing corrective tasks to solve this issue. I'm sure there is a reason causing the error I've not discovered yet to fix this once and for all.

Hopefully somebody here will shed some light on this. I will be really grateful.

Thanks in advance.


texts: Does a database restore remove existing stored procedures

texts:
I have a PROD and NON-PROD system

NON-PROD already has a copy of an application PROD database

I have also created new stored procedures on the NON-PROD application database and made changes to a few old ones

If I were to take a fresh backup of the PROD application database, and were to restore it on the NON-PROD server, will this:

remove the new stored procedures which I created on the NON-PROD application database?
remove the changes made to the stored procedures (NON-PROD application database) which have the same name as on PROD application database, and replace them with the original one's from PROD application database?


texts: How do I get more details on a full text catalog error?

texts:
I have an error that is repeatedly generated on a SQL Server 2005 instance running the databases for MS Team Foundation Server.

 'Severity 16' occurred on INSTANCENAME

Looking in the SQL Server logs gives me

 Errors were encountered during full-text index population for table or indexed view '[TfsWorkItemTracking].[dbo].[WorkItemLongTexts]', database 'TfsWorkItemTracking' (table or indexed view ID '1810105489', database ID '14'). Please see full-text crawl logs for details.

And the actual full-text crawl log (\MSSQL\LOG\SQLFT0001300005.LOG) gives me:

 Error '0x800b0100' occurred during full-text index population for table or indexed view

 The component 'MSFTE.DLL' reported error while indexing. Component path 'D:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Binn\MSFTE.DLL'

If I rebuild the full-text catalog like so...

 ALTER FULLTEXT CATALOG [TeamFoundationServer10FullTextCatalog]
 REBUILD

The error will stop happening for a while, but it will start up again days later. I have searched for hours and cannot figure out what is causing this. Any suggestions?


texts:
I'm trying to resize a couple of tablespaces but the following query is only valid in Oracle 11g.

alter tablespace 2 DATAFILE '/appli2/oracle/xxx/oradata02/xxx_undo_01.dbf' resize 3g;

In oracle v10g i try with this query, picked from oracle documentation :

alter DATABASE DATAFILE '/appli2/oracle/xxx/oradata02/xxx_undo_01.dbf' resize 3g;

I work with Oracle 10g and this query throws error message according to a non valid ALTER DATABASE modifier.

Error messages:
ERROR at line 1:
ORA-02140: invalid tablespace name

Thanks in advance.


texts: SQL Server - update all data in all tables from backup

texts:
I need to update data (all tables) on our QA environment by replacing it with our latest Prod Database data (Make QA more actual). As I understand, by default if I do it with SSMS it will also replace Security options and Roles which I want to avoid:

Our Prod and QA DBs are on different servers so service accounts
regulating activity on servers are also different
AD groups are different, so if I just restore DB from Prod on QA, I
will give access to QA to some users
Any other unexpected issue

Is it possible somehow to replace data in all tables (Tables on QA and Prod are identical, difference only in amount of data) from DB backup file and do not touch anything else?

As I understand through Google my only variant is to restore Prod DB on QA server with different name, truncate tables on QA and use SQL MS Wizard but I hope that there are other options.

3rd party compare tools is not an option because server owner is not allow to use it. Only possible options is something free from Microsoft. It's need to be done just one time.


texts: How to query with filter classifications

texts:
Looking for a way to filter clothes based on their labels, the labels need to be considered for their classifications, such that different classification all needs to be satisfied (AND), same classification is joined (OR).
E.g. The following expectation can be satisfied:
Ask: (Blue(classification: Color) or Green(classification: Color)) and On Sale(classification: Discount)
or
Ask: Blue(classification: Color) and (Aloha(classification: Style) or Knit(classification: Style))
Need help understand the best way to achieve this.
SELECT
    "clothes".*
FROM
    "clothes"
INNER JOIN "clothes_labels" ON
    "clothes_labels"."clothes_id" = "clothes"."id"
INNER JOIN "labels" ON
    "labels"."id" = "clothes_labels"."tag_id"
WHERE
    ...

Sample query fiddle (thank you):
https://dbfiddle.uk/?rdbms=postgres_13&fiddle=fd625a0f5b97a6e6b3067758aadcd434


texts: How to find an entry from filtering with two associated tables

texts:
This questions has spinned off of another question I probably did not ask correctly from the get go.

I have some tables, where attributes and attributes_entries that hold a pair of dynamic values for products.

The user table (just used as an example):

id     name       whatever
22     thanos     big guy

The products table:

id   code    price
659  0H040   99.99
660  0H040   99.99

The attributes table:

id   title           model      model_id
1    nickname        products   659
2    manufacturer    products   659
3    rhod            products   660
4    manufacturer    products   null
5    age             user       null
6    age             user       22

The attribute_entries table:

id     title      FK_attribute_id
1      windstar   1
2      ford       2
3      75.3       3

I have to find the product that matches the attributes and attribue_entries a user is looking for.

So, if the search filter is manufacturer=ford&nickname=windstar and these are found for product 659 then provide in data set.

How would I go about doing this?  I have a fiddle with queries from my previous question but I can't seem to get the proper results.

The desired result is something along the lines of:

attr_title      attr_entry_title    attr_model      attr_model_id
nickname        windstar            products        659
manufacturer    ford                products        659

But what I really need is to get the product id that simply has a match of both these value pairs manufacturer=ford&nickname=windstar.

Since I do not have a FK association to products, I think this is going to be a problem.  I didn't associate the products because some attributes are entered globally under a model like products or users, so when someone enters a product or user they can see a dynamic input under attributes called manufacturer, and can enter a value for this to associate it to the model (ex.products).

I think I'm going to have to redo this structure since a reverse lookup of attributes and attribute_entries I have no idea how to find its model id value that has no association.


texts:
I have this query that runs fine for a couple of days (from the application) and then slows down to where it can't even finish.

If I rebuild the plan, it flies.

There is a signifigant amount of data being modified in the tables that it is using.

Also if I run it from SSMS and turn ARITHABORT on, it works fine. If I turn ARITHABORT off, it hangs and hangs.

Side note - Stats are repopulated every Monday. Today (Thursday) the plan is bad. The table in question has 1.4 billion records with 9 million changed since Monday.

Is there a way I can increase performance so I don't have to rebuild this plan?

Bad Plan

Good Plan


texts: Powershell Invoke-Sqlcmd Replacing £ with?

texts:
When I create a SQL Script that inserts the value '£' or '€' into a table the value on the table is a question mark (?)
Though, when I insert '$' it inserts a dollar sign.

Anyone know what is going on and how to make sure a pound sign is inserted into the table?


texts: Integrate MySql Full Text searches in CMS

texts:
I'm developing my own CMS and using MySql v5.5 DB to save the content items records.

Due to a technical limitations, I can't upgrade my database to a newer version that support Full-Text searches on InnoDB tables.

Would it be a good practise to use MyISAM tables for textual searches while the "real" data is saved in InnoDB storage engine?

Example:

The user create content item and save it to the database
All the content item fields are saved to an InnoDB table.
All textual fields are STACKED together and saved in a single field inside a MyISAM table which also include additional ID field with reference to the original content item.

When the user perform a textual based search, I'll query against the MyISAM table using a Full-Text search query which will give me references to all the matching records in my InnoDB table.

The bottom line - Is that considered to be a good solution performance-wise and for improving the searching capabilities of my CMS, or should I stick with the good old LIKE operator and query using only the InnoDB table?


texts: Is it normal for Snapshot Replication job to be constantly running?

texts:
Our SQL Server 2016 already has Merge replication running successfully for some tables in 2 databases. We then needed to restore Snapshot replication for another set of tables between same pair of databases. I have run a T-SQL script from old server to restore Snapshot publication/subscription. Everything went fine, publication and subscription were created. I have manually run the Snapshot Agent for the first time and then manually triggered synchronization via the View Synchronization Status dialog.

All articles in the publication had Drop and Re-Create setting for the destination tables. After initial synchronization finished I have compared source and destination tables and have confirmed number of rows to be equal. Just what we needed.

However, I am surprised that after delivering the initial snapshot the replication process didn't finish like it does for example for Merge replication. Instead, the View Synchronization Status dialog reports synchronization as running but with No replicated transactions are available message, see below:

I then went to check synchronization history for this subscription and found out that the actual synchronization has been running for already 20+ hours without actual rows/tables copied. Looks like it has been waiting for new changes/snapshots all this time, see below:

The above image correctly reports bulk copying of changes around 3 am because the REPL-Snapshot agent job has been put on schedule to run at 3 am each day. However, from the SQL Server Agent Job monitor I see that REPL-Distribution, REPL-LogReader and REPL-QueueReader jobs for my snapshot publication are constantly running.

The question is: is the above behavior a normal one? Shouldn't Snapshot replication job just process a new snapshot and then quit? Or are we missing schedules for Queue and Log reader jobs too? I worry for not being able to free up Transaction Log if replication never finishes.

In the T-SQL script for this publication/subscription we had these lines:

exec [DB1].sys.sp_addlogreader_agent @job_login = null, @job_password = null, @publisher_security_mode = 1
GO
exec [DB1].sys.sp_addqreader_agent @job_login = null, @job_password = null, @frompublisher = 1
GO

Not sure if they cause the synchronization to be always running. Any insight on what is going on here will be very much appreciated.


texts:
I have an rman database backup from Oracle 11g XE which i am trying to import into oracle 18c XE database using below commands

rman target /
shutdown abort;
startup nomount;
SET DBID DBID_FROM_ORACLE11G
restore controlfile from 'PATH_TO_CONTROL_FILE';
alter database mount;

After "alter database mount" I am getting below error

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 05/29/2019 11:12:45
ORA-65101: container database set up incorrectly

Is there something I am missing or is there anything wrong with my procedure


texts: SQL: What is slowing down INSERTs if not CPU or IO?

texts:
We have a database for a product that is write-heavy. We just bought a new server machine with a SSD to help. To our surprise, the insertions were not faster than on our old machine with much slower storage. During benchmarking we noticed that the IO rate exhibited by the SQL Server process was very low.

For example, I ran the script found on this page, except that I added a BEGIN TRAN and COMMIT around the loop. At best I could see the disk usage reach 7Mb/s, while CPU barely touched 5%. The server has 64Gb installed and is using 10. The total run time was 2 minutes 15 seconds for the first call down to around 1 minute for subsequent calls. The database is on simple recovery and was idle during the test. I dropped the table between each call.

Why is such a simple script so slow? The hardware is barely being used at all. Both dedicated disk benchmarking tools and SQLIO indicate that the SSD performs correctly with speeds upward of 500Mb/s for both reading and writing. I understand that random writes are slower than sequential writes, but I would expect a simple insert like this, to a table without clustered indexing, to be much faster.

Ultimately our scenario is much more complex, but I feel that I need to understand a simple case first. In a nutshell our application deletes old data, then uses SqlBulkCopy to copy new data to staging tables, performs some filtering, and finally uses MERGE and/or INSERT INTO depending on cases to copy the data to the final tables.

--> EDIT 1: I followed the procedure linked by Martin Smith, and I got the following result:

[Wait Type]  [Wait Count] [Total Wait (ms)] [T. Resource Wait (ms)] [T. Signal Wait (ms)]
NETWORK_IO          5008              46735                 46587        148
LOGBUFFER           901               5994                  5977         17
PAGELATCH_UP        40                866                   865          1
SOS_SCHEDULER_YIELD 53279             219                   121          98
WRITELOG            5                 145                   145          0
PAGEIOLATCH_UP      4                 58                    58           0
LATCH_SH            5                 0                     0            0

I find it weird NETWORK_IO takes most of the time, considering there are no result to display and no data to transfer anywhere other than to the SQL files. Does the NETWORK_IO type includes all IO?

--> EDIT 2: I created a 20Gb RAM disk and mounted a database from there. The best time I had on the SSD is 48s, with the RAM disk it went down to 37 seconds. NETWORK_IO is still the biggest wait. The maximum write speed to the RAM disk was about 250Mb/s while it's able to do multi gigabytes per second. It still wasn't using much CPU, so what's holding up SQL?


texts:
We have a table called Feedback as follows :

id            - int(10) unsigned    NO  PRI     auto_increment
vendor_Id     - int(10) unsigned    YES MUL
created_At    - datetime CURRENT_TIMESTAMP
updated_At    - datetime(3)
code          - int(11) YES
feedback      - varchar(300)    YES

CREATE TABLE `Feedback` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `vendor_Id` int(10) unsigned DEFAULT NULL,
  `created_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_At` datetime(3) NOT NULL,
  `uniqueKey` varchar(255) DEFAULT NULL,
  `customerContactNumber` varchar(15) DEFAULT NULL,
  `customerName` varchar(30) DEFAULT NULL,
  `code` int(11) DEFAULT NULL,
  `feedback` varchar(300) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `feedback_unique` (`uniqueKey `),
  KEY `codeIndex` (`vendor_Id`,`updated_At`,`code`),
  KEY `feedbackIndex` (`vendor_Id`,`code`,`feedback`,`updated_At`),
  CONSTRAINT `feedback_vendor_id_foreign` FOREIGN KEY (`vendor_Id`) REFERENCES `Vendor` (`vendor_Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

and we need to create index for our Feedback Table.

So we added 3 indexes based on fields as part of where clause :

 index1 (vendor_Id, updated_At);
 codeIndex (vendor_Id, updated_At, code)
 feedbackIndex(vendor_Id, code, feedback, updated_At)

Now when we run the following query :

explain  select  'id', 'customerName', 'customerContactNumber', 'feedback', 'updated_At'
    from  Feedback
    where  vendor_Id = 1
      and  updated_At <  '2017-11-15 12:58:12.387'
      and  updated_At >= '2017-11-01 00:00:00.000'
      and  code is not null
      and  code >=0
      and  code <= 6
    order by  updated_At DESC
    limit  10

the mysql output says :

possible_keys : index1,codeIndex,feedbackIndex
keys : feedbackIndex

Am curious to know why the explain statement shows 'feedbackIndex' as the key selected. We were expecting it to select 'codeIndex'

There are other 2 queries :

explain  select  code, count(*) as count
    from  Feedback
    where  vendor_Id = 1
      and  updated_At <  '2017-11-15 12:58:12.387'
      and  updated_At >= '2017-11-01 00:00:00.000'
      and  code is not null
    group by  code

===> shows 'codeIndex' as key in output of explain statement. which is as  expected

explain  select  code, count(*) as count
    from  Feedback
    where  vendor_Id = 1
      and  updated_At <  '2017-11-15 12:58:12.387'
      and  updated_At >= '2017-11-01 00:00:00.000'
      and  code is not null
      and  feedback is not null
    group by  code`

===> shows 'feedbackIndex' as key in output of explain statement. which is as  expected

I can not figure out why our first query is showing feedbackIndex as the keys.


texts: I have a quick rman Backup question. If I would like to run RMAN backups on my (dataguarded) 11g physical standby database, what status should that standby db be in? At the moment the standby is in mount (managed recovery) mode, so a remote connection from isn’t possible. What do I need to do to allow a third party media management backup software product like legato/netbackup/networker to access the db? Many thanks.


texts: API Manager, anaylytics and MYSQL Connection Time Out Issue

texts:
I keep getting a connection timed out error message. The following are my connection configuration properties and the connection timed out exception is also below. for some reason, it keeps timing out after 15 minutes.
my.cnf
[mysqld]
wait_timeout = 86400
interactive_timeout = 86400
connect_timeout = 120

worker/deployment.yaml
  // APIM_ANALYTICS_DB analytics database
  jdbcUrl: 'jdbc:mysql://some_ip:some_port/api-manager-stats?useSSL=false&autoReconnect=true'
  username: some_username
  password: some_password
  driverClassName: com.mysql.jdbc.Driver
  maxPoolSize: 300
  idleTimeout: 288000000
  connectionTestQuery: SELECT 1
  validationTimeout: 300000000
  isAutoCommit: false

dashboard/deployment.yaml
// main wso2 API manager database WSO2AM_DB
jdbcUrl: 'jdbc:mysql://some_ip:some_port/api-manager-admin?useSSL=false&autoReconnect=true'
username: some_username
password: some_password
driverClassName: com.mysql.jdbc.Driver
maxPoolSize: 10
idleTimeout: 60000
connectionTestQuery: SELECT 1
validationTimeout: 30000
isAutoCommit: false

// analytics database config APIM_ANALYTICS_DB
jdbcUrl: 'jdbc:mysql://some_ip:some_port/api-manager-analytics?useSSL=false&autoReconnect=true'
username: some_username
password: some_password
driverClassName: com.mysql.jdbc.Driver
maxPoolSize: 50
idleTimeout: 60000
connectionTestQuery: SELECT 1
validationTimeout: 30000
isAutoCommit: false

Error Message

ERROR {org.wso2.extension.siddhi.io.mgwfile.task.MGWFileReaderTask} - Error occurred while publishing micro-gateway API Usage data. org.wso2.extension.siddhi.io.mgwfile.exception.MGWFileSourceException: Error occurred while getting the next files to process.
  at org.wso2.extension.siddhi.io.mgwfile.dao.MGWFileSourceDAO.getNextFilesToProcess(MGWFileSourceDAO.java:155)
  at org.wso2.extension.siddhi.io.mgwfile.task.MGWFileReaderTask.run(MGWFileReaderTask.java:59)
  at java.util.TimerThread.mainLoop(Timer.java:555)
  at java.util.TimerThread.run(Timer.java:505)
    Caused by: java.sql.SQLTransientConnectionException: HikariPool-4 - Connection is not available, request timed out after 937982ms.
  at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:697)
  Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
 The last packet successfully received from the server was 2,101,021 milliseconds ago.  The last packet sent successfully to the server was 937,983 milliseconds ago.
  at sun.reflect.GeneratedConstructorAccessor73.newInstance(Unknown Source)
  at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
Caused by: java.net.SocketException: Connection timed out (Read failed)

Question
Is there some other configuration I have to add or will the right combination of these connection properties make the error go away?


texts:
I would like to know if there is a way that I can restore my databases, I don't have a replication and I would like to migrate my databases without downtime or losing data.
So with binlogs could I do that? I have in my new server already a database but is kinda old, could this be possible.
Thanks in advance


texts: Connecting to SQL Server 2014 with sqlcmd

texts:
I am using the version of sqlcmd that comes with the Microsoft ODBC Driver 11 for Sql Server to connect to a remote 2014 instance.  I can create a DSN connection using the SQL Server Native Client 11.0 just fine.  Port 1433 is open and I can ping, telnet and all that good stuff.  Seems to be a driver compatibility issue as I get the same error when trying to create a System DSN with the ODBC driver. Any thoughts?

Where can I get a compatible version of sqlcmd? Is there something I am missing?


texts: Setting innodb_buffer_pool_size in for Mysql

texts:
We have a dedicated DB box with with 8 GB of RAM and running Redhat linux 64 bit OS. Our MySQL Data + Index size almost 7.8 GB. About setting innodb_buffer_pool_size, I have read 2 suggestions

Set it to 80% of RAM available on a dedicated DB server.
Set at-least 10% above the size of Data + Indexes.

We cannot satisfy the second condition in my case. So will it have negative effect on Database performance?

Will having innodb_buffer_pool_size of like 6GB affect the performance?


texts: Database security through stored procedures

texts:
Possible Duplicate:
  Do stored procedures prevent SQL injection?

I'm curious about some database security techniques I've been learning in class recently.
Namely, I have implemented a SQL Server 2008R2 with stored procedures that a connection user can execute...
I continually get a server error processing url when attempting injection attacks and my DB tables remain untouched - I have read that a stored procedure is not a defense against injection - are more complex injections required to break it or are there reasons why simple drop table, list client, etc. injections are failing?
examples:

a';DROP TABLE CreditCard; SELECT * FROM Client WHERE 't' = 't

x' AND 1=(SELECT COUNT(*) FROM CreditCard); --

results are essentially:

exec sp_GetUser 'x' AND 1=(SELECT COUNT(*) FROM Client); --' , 'monkey'

  An error occurred on the server when processing the URL. Please contact the system administrator.

Here's the login stored procedure, incase that has some influence over why I'm getting the results I am.

USE [CIS413]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_GetUser]

    @UserName varchar(50), @Password varchar(20)
AS
BEGIN
    SELECT userName from users where userName = @UserName and userPass = @Password
END


texts:
I have 2 HP servers with CntOS 6 as OS and DB2 10.5 as Database. there 2 servers are HADR. standby server suddenly rebooted and when i checked log it said that OS had killed 4 db2 demons.

IBM.ConfigRM
ctcasd
RMCD_INFO_1_ST
GS_STOP_ST

why this happens? why OS killed these demons?
there is no explaination in diag or other log file to be checked.


texts:
PostgreSQL 9.2 introduced the json field type.  Why and when should I use it?  What benefits does it have over a text field?

I thought there were new query options available, however I haven't seen any.  Am I missing something?


texts:
I need to make database model about libraries. In that model I'm tracking more libraries and every library has it's own moderators. Every moderator can make categories of the books and than add books in categories (note: another library doesnt need to have same as categories of books as library where that moderator is working, every other library can have different categories of books .. ).

This picture represents how did I make it. But something is missing, think this is not good solution. I have also another tought.. To link  tables "book" and "category_book" in N:M and than link "book" and "libraries" which from I can get everything I need. But, think it's also not the best solution because every library will in that moment has same categories of books.

Am I missing something or? Haven't met with this kind of problem yet so if you could help me I will be grateful

Whole design ...Note: it's not over, I can't move further beacuse of this problem


texts: Find a username within instance wide objects

texts:
A user's AD account keeps being locked out. The latest idea from colleagues is that their username and password is hard-coded in some database in the instance (a stored procedure for example).

Is this possible?
How would I search the instance for where this could be?


texts: Moving data from an old table into a newly created table with different field names / number of fields

texts:
I've been handed over management of a database, and I have had very little exposure to MySQL (or databases in general) so far.

I would like to do two things, both of which are (I expect) fairly simple, however it is the combination of the two which is causing me difficulty.

At present, I have one very large table containing all data. It can easily be divided into three smaller tables, as one of the fields effectively has only three distinct values, by which I wish to separate it. I assume writing a query to find entries of each value and copy only the correct one across to a new table would be very simple.

However, my problem is that the fields in this table are very, very poorly named and misspelled. So, when I create my new tables I would like to rename many of the field names. I will also potentially need to add fields.

Is there a way to achieve this so that I may create new tables with correctly named fields and then copy the data across without any intermediary steps?

The two obvious (though very clumsy) ways to do this that I can see would be to -

Copy the current table, rename all the fields to match the newly designed tables, and then copy the data across since the field names match. (This is not ideal as the current table is very large, and the issue of additional fields in the new tables may still cause issues?)
Create the new tables with the same poor field names as the original. Copy the data across. Change the new table's field names.

Both these potential methods seem very clumsy. Is there a way to do this that simply lets me create a new table with correct field names and then transfer the old data easily at any time?

(Please forgive me if this is an obvious or awkward question, I'm very, very new to this. Thank you.)


texts: multiple groupings in select clause in postgres

texts:
I have the following orders table in a postgres 9.6 db:

orders
item_id, created_at (a timestamp ie Rails app)

I'd like to get back a list of item_ids and distinct years and a count like this:

item_id  year   count
-------  ----   -----
23       2017   23
24       2017   45
24       2018   11

How would I do this? I tried something like this but am stuck:

select item_id, extract(year from created_at) from orders where group by item_id, created_at;


texts: Aren't two writes required to update a clustered index record

texts:
I was reading the article on indexes at simple-talk, where it is written that

  If a heap has a non-clustered index on it (as the primary key), and data is inserted into the table, two writes have to occur. One write for inserting the row, and one write for updating the non-clustered index. On the other hand, if a table has a clustered index as the primary key, inserts take only one write, not two writes. This is because a clustered index, and its data, are one in the same. Because of this, it is faster to insert rows into a table with a clustered index as the primary key than it is to insert the same data into a heap that has a non-clustered index as its primary key. This is true whether or not the primary key is monotonically increasing or not.

Isn't it wrong? Clustered indexed naturally resort to B+ trees, where only the keys are stored in intermediate nodes (unlike B-trees, where the entire record is stored. That is why B+ trees can fit more keys in a single page, resulting it being bulky in width but shorter in height), so all records are stored in the leaf pages (the pages themselves being logically sorted through linked-lists, while the data in each page being physically sorted). So if a record has to be updated, say the value 1 has to be updated to 7, won't the update need to be applied to both the key in the clustered index top node (this may, in cases, cause a re-structuring of the entire structure) and the corresponding value in the record in the leaf-page?

Update: Okay, I did some study, and found that apart from the initial tree structure (where some values must be present twice, for example the key values in the node), when new values are inserted, they just fit into the leaf page, whereas the tree gets restructured to accommodate that. However, when, say 5 values are inserted, the 3rd value may cause the first inserted value (which is currently occupying only leaf level space) to cascade up, thus causing it to be written twice (once in the leaf level, other in the index level). Of course, such re-writes (although they don't occur at the time of insertion, they can occur later) will be much less compared to the two-time write which occurs each time there is an insertion into a heap with NCI,  but still isn't it wrong to say that there is no re-write?


texts: Does SQL Server 2017 Express support Linked Server connections to Oracle DB

texts: Is there any restrictions with "Linked Servers" with SQL Server 2017 Express?  Vs using SQL Server 2017 Standard.   Only in regards to "Linked Servers".


texts: One CPU in a 32-core system is getting to 100% usage and hurting performance

texts:
I am experiencing a problem with one server. It has 32 cores and seems to be well-configured in all aspects (memory, hard disk drives...). Performance Dashboard is not showing blocks or deadlocks and no waiting tasks. All queries seem to be performing well.

From time to time (every 5-6 minutes) one of the cores gets up to 100% utilization and the system's performance slows down. It lasts for some seconds.

What should I check to know what is causing this misbehaviour?

It is a SQL Server 2005 standard edition with Service Packs installed.

Thank you so much.


texts: SQL Server Logical Device for Directories

texts:
I am in need of logical backup device option for SQL Server, but not for disk files, but Windows directories. Is there anything like this available or how can I achieve that? Even from 3rd parties?
I have different folders on different machines and I need one logical name for them both, sure one on each server.
I need to create one logical directory in several servers with the same name, but their paths in actuality are different. So I can create a logical backup device with a path, which is like this c:/myfolder/backup.bak and all the backups initiated to this device will be appended in the same file. But I need to create it for the c:/myfolder directory (aka folder) without specifying the file backup.bak. so it will reference to a folder and not to a file, so I can create different backups in the folder with different names and separate files.


texts: Can anyone please tell me if these are correct statements about measure group partitioning in ssas?

texts:
A partition is based either on a table or view in a data source, or on a table or named query in a data source view. The location where partition data is stored is defined by the data source binding. Typically, you can partition a measure group horizontally or vertically:

In a horizontally partitioned measure group, each partition in a measure group is based on a separate table. This kind of partitioning is appropriate when data is separated into multiple tables. For example, some relational databases have a separate table for each month's data.
In a vertically partitioned measure group, a measure group is based on a single table, and each partition is based on a source system query that filters the data for the partition. For example, if a single table contains several months data, the measure group could still be partitioned by month by applying a Transact-SQL WHERE clause that returns a separate month's data for each partition.

It seems like it should be the opposite.


texts: Checking if entities match an id in on relation where it could show up in either of two columns

texts:
The problem
I'm trying to make a page for a website (with PostgreSQL database) that allows the owner of a group to add new members from their friends list.
Given the owner_id of a Group its group_id I need to retrieve only the user_ids from users who are both friends with the owner, and not in the group.
The relations being queried
I need to use the following relations

UserFriend: Stores two user_ids to connect those two users from the User relation to each other as friend.
UserGroup: Stores a user_id and group_id to connect the Group relation to its members in the User relation.

The two relations are described as follows:
UserFriend
  Column   |  Type   | Collation | Nullable | Default
-----------+---------+-----------+----------+---------
 user_id_a | integer |           | not null |
 user_id_b | integer |           | not null |

UserGroup
  Column  |  Type   | Collation | Nullable | Default
----------+---------+-----------+----------+---------
 user_id  | integer |           | not null |
 group_id | integer |           | not null |

My attempt so far with example data
For example, given the data below, I want to find all users that are friend with user 3, and not in group 3.
SELECT user_id_a, user_id_b FROM UserGroup UG, UserFriend UF
    WHERE (UF.user_id_a = 3 AND UF.user_id_b != UG.user_id)
       OR (UF.user_id_b = 3 AND UF.user_id_a != UG.user_id)
       AND UG.group_id = 3 GROUP BY user_id_a, user_id_b;

Expected Result
 user_id_a | user_id_b
-----------+-----------
         1 |         3

Even better if it could just show something like
 possible_members
------------------
                3

Actual Result
 user_id_a | user_id_b
-----------+-----------
         1 |         3
         3 |         2
         3 |         4

Example data
UserFriend
 user_id_a | user_id_b
-----------+-----------
         1 |         2
         1 |         3
         1 |         4
         3 |         4
         3 |         2

UserGroup
 user_id | group_id
---------+----------
       1 |        1
       2 |        1
       3 |        1
       4 |        1
       1 |        2
       2 |        3
       3 |        3
       4 |        3

Analysis
I'm fairly certain that the issue has something to do with the way I'm handling the fact that the user_id I'm trying to match could be in either column of UserFriend, but I'm not sure how to fix it. I've also tried using JOINs without any luck.


texts: Transactional Replication Synchronization taking too long to respond

texts:
I successfully generated the snapshot, but subscription is not working correctly. Either the synchronization takes too long to respond or it simply displays the text shown in below attached screenshot.

Here is the successful snapshot generation:

It is only retrying and not giving the end result.


texts: Finding matches where GROUP BY COUNT(*) is 1

texts:
Say I have tables

organisation:

id | name
---------
1    ACME
3    MGM

org_type:

type_id | org_id
----------------
1         1
2         1
2         3

How do I query a list of org names where there type is 2, and no other type is linked with that organisation? In other words, a query that returns only organisation with org_id of 3 from above data.

I'm trying queries like

SELECT o.name
FROM organisation o
JOIN org_type ot ON o.id=ot.org_id
WHERE ot.type_id = 2
GROUP BY ot.org_id
HAVING COUNT(*) = 1

But obviously that just returns the list of ALL orgs where a link to type_id 2 exists. I only want those where no other link exists.


texts:
In my company there is MS SQL SERVER 2008 standard edition installed.
My problem is we want to encrypt whole database but in standard edition there is encryption like transparent-data-encryption(TDE) not available and column level encryption is time consuming.
So i want to know is there any alternative method available in sql server gor whole database backup?


texts:
If I try to run the postgres command, I get:
2020-12-29 00:14:53.213 GMT [34374] LOG:  skipping missing configuration file "/usr/local/pgsql/data/postgresql.auto.conf"
postgres: could not find the database system
Expected to find it in the directory "/usr/local/pgsql/data",
but could not open file "/usr/local/pgsql/data/global/pg_control": No such file or directory

How can I find out what config file is telling postgres to look at /usr/local/pgsql/data?
In my case, postgres was installed on OSX via Homebrew.


texts:
I would like to be able to generate random bytea fields of arbitrary length (<1Gb) for populating test data.

What is the best way of doing this?


texts: Basic ERD analysis for a martial arts business domain

texts:
I am working on a basic website in order to support querying results from martial arts tournament data. We use participation data to determine things like who gets to host tournaments, regional awards, etc.

Example queries would be along the lines of:

Total participants by school per tournament
Total participants by school owner (Owners can have multiple schools)
Top performing school (participant placing)
Most heavily entered age/rank divisions
Total entries per event

My current diagram

I have the following entity relationship diagram (ERD) (With the tournament "table" being truncated, there are 27 attributes that include rank, bracket, placing in each event, name, age, etc). It's fairly simple, but at this point I think it is all I need. (Future advancements will include analysis of judging participation, etc).

Questions

For the ERD, do I necessarily need the one-to-many (1:M) relationship indicator between the school table and the results table? They will be used in queries, but are not necessarily tied to each other.
And, is the basic diagram correct in its layout?


texts: Composite index of three columns in MySQL and WHERE by 1st and 3rd column. Index usage

texts:
Let's consider a table with 3 varchar columns and a composite index on all of them in the order of (a,b,c):

CREATE TABLE `test` (
      `a` VARCHAR(255) NOT NULL,
      `b` VARCHAR(255) NOT NULL,
      `c` VARCHAR(255) NOT NULL,
      INDEX(`a`,`b`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET='utf8';

Will the composite index be used in case of WHERE a = const AND c = const condition? Here it is stated it couldn't be used, however, if we launch EXPLAIN for such a table/query on a DB Fiddle we see a in possible_keys and keys, so looks like it is going to be used at least for the first part of the condition.

UPDATE: I think I used wrong wording in my initial question. I would like to know if the composite index could be used in case of WHERE a = const AND c = const and to object to the following clause in the link above:

  Notice that if you have a composite index on (c1,c2,c3), you will have
  indexed search capabilities on one the following column combinations:

  (c1) (c1,c2) (c1,c2,c3)

I do realize that it could not be used in case of (c2,c3), but I'm asking about (c1,c3)


texts: Can SQL Server Management Studio Express be installed standalone?

texts:
I was wondering if the SQL Server Management Studio Express (mainly for Microsoft SQL Server) be installed on a standalone machine (without any of the SQL Services and stuff) to connect to a remote database?

I run a SQL Server 2012; I have been told by one of my managers that they wish me to create a web UI for specified users to run queries / update the database; this is within my ability of PHP; but I was thinking for ease, I could make a proposition to scrap the need for the Web UI because this will cost a lot of time for our server administrators, but install SMSE on our network machines to connect to/manage our databases?

Is this possible? or would I have to go through the painful task of creating a WebUI which displays schemas, tables etc.


texts: Which of these structures are good for maintaining history data?

texts:
Iam creating an application which is being used to send reminders. Here is the requirements.

We have set of Students.
Every student has a Student Pass, this need to be renewed in every month. Student Pass has Pass ID, monetary value, date of expiry, etc.
Before 7 days of Student Pass expiry, we have to send notifications to the system admin.
We have to monitor the Student Pass History of every student as well. Which means how many notifications were given to the admin, what is the date the pass got expired and so on.

Now we need to create tables for this. We thought of creating a table called StudentPass and maintaining the student pass history and current student pass in the same table. It is like below

However maintaining the current student pass data and student pass history seems to be
mixed up work because we have to send notification for the expiration of current student pass. So we decided to divide this into 2 tables. It is like below, the current student pass data will be maintained in StudentPass table and the history data will be maintained in StudentPassHistory.

With your experience in database, please let me know which method is better. In case you need more information about why we divided the data into 2 tables, that is because our notification system can simply identify that it should send notification only for the items in StudentPass table, We do not have to do DB comparison to find out which data is the latest and current, we have history and current data separated and it will be easy to generate reports. Your advice is highly appreciated.


texts:
I have a table with more than 15m rows. I need the total number of rows. So:

SELECT COUNT(*) FROM thetable;

Which takes around 50s to complete. Explaining gives me Select tables optimized away. I suppose this means that the result can be found only by using an index, then why does it still take so long? Here are some information about the index on the id column (It's non nullable):

  Index Type: BTREE (clustered)

  Cardinality: 14623100

  Unique: YES

How can I improve the performance of this query? Thanks.

Note: The database is MySQL 5.7.1 and using InnoDB engine.

EDIT:

Create statement:

CREATE TABLE `properties` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `address` varchar(255) DEFAULT NULL,
  `locality` varchar(50) DEFAULT NULL,
  `latitude` decimal(13,9) DEFAULT NULL,
  `longitude` decimal(13,9) DEFAULT NULL,
  `state` varchar(10) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  .....
  PRIMARY KEY (`id`),
  KEY `index_properties_on_address` (`address`),
  KEY `index_properties_on_latitude` (`latitude`),
  KEY `index_properties_on_longitude` (`longitude`),
  KEY `index_properties_on_state` (`state`),
  KEY `index_properties_on_created_at` (`created_at`),
  .....
) ENGINE=InnoDB AUTO_INCREMENT=28267712 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED;

Note: I omitted some lines, there are 44 columns.

Explain plan:

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered |            Extra             |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | NULL     | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+


texts:
I have an SQL select, that gives two rows back.
The SQL update gives the error message 1093 back;

  mysqli error: [1093: Table 'HD_TICKET' is specified twice, both as a
  target for 'UPDATE' and as a separate source for data]

update HD_TICKET, HD_STATUS as T5
    set HD_TICKET.HD_STATUS_ID = T5.ID,
HD_TICKET.TIME_OPENED  = IF(T5.STATE = 'opened', NOW(), HD_TICKET.TIME_OPENED),
HD_TICKET.TIME_CLOSED  = IF(T5.STATE = 'closed', NOW(), HD_TICKET.TIME_CLOSED),
HD_TICKET.TIME_STALLED = IF(T5.STATE = 'stalled', NOW(), HD_TICKET.TIME_STALLED),
HD_TICKET.SATISFACTION_RATING = IF(T5.STATE = 'closed', NULL, HD_TICKET.SATISFACTION_RATING),
HD_TICKET.SATISFACTION_COMMENT = IF(T5.STATE = 'closed', NULL, HD_TICKET.SATISFACTION_COMMENT),
HD_TICKET.TITLE = (SELECT NAME FROM ORG1.ASSET WHERE ORG1.ASSET.ID = (SELECT ASSET_ID FROM HD_TICKET WHERE HD_TICKET.ID = HD_TICKET.PARENT_ID))
  where T5.NAME = 'New' and
HD_TICKET.HD_QUEUE_ID = T5.HD_QUEUE_ID and
    (HD_TICKET.ID in (<TICKET_IDS>))

Can anybody help me, please? Thanks!


texts: Select query for returning different data based on creation time

texts:
I have a view, which will select data from table reports. The select will filter the data based on multiple dates.

reports table: id, key_id, position, created_at

My select:

pr.key_id,
COALESCE(CASE WHEN date(pr.created_at) = date(current_date - interval '1' day) THEN array_agg(DISTINCT pr.position) ELSE '{0}' END ) AS yesterday,
COALESCE(CASE WHEN date(pr.created_at) = date(current_date - interval '2' day) THEN array_agg(DISTINCT pr.position) ELSE '{0}' END ) AS last_week,
COALESCE(CASE WHEN date(pr.created_at) = date(current_date - interval '3' month) THEN array_agg(DISTINCT pr.position) ELSE '{0}' END ) AS last_month,

Even if I group by key_id and date the result is not grouped.

Example of result I get now:

key_id: 1 yesterday: {1} last_week: {0} last_month: {0} created_at: '2014-04-11'
key_id: 1 yesterday: {0} last_week: {1} last_month: {0} created_at: '2014-04-10'
key_id: 1 yesterday: {0} last_week: {0} last_month: {1} created_at: '2014-04-09'

What I expect:

key_id: 1 yesterday: {1} last_week: {1} last_month: {1} created_at: '2014-04-11'

With this query I get what I want. Is there a better way of doing it?

SELECT
    k.name,
    array_agg(DISTINCT position),
    (SELECT array_agg(DISTINCT position) FROM project_reports prk WHERE prk.project_id = pr.project_id AND prk.keyword_id = pr.keyword_id AND date(prk.created_at) = date(current_date - interval '1' day)) as y_position,
    (SELECT array_agg(DISTINCT position) FROM project_reports prk WHERE prk.project_id = pr.project_id AND prk.keyword_id = pr.keyword_id AND date(prk.created_at) = date(current_date - interval '7' day)) as last_week,
    (SELECT array_agg(DISTINCT position) FROM project_reports prk WHERE prk.project_id = pr.project_id AND prk.keyword_id = pr.keyword_id AND date(prk.created_at) = date(current_date - interval '1' month)) as last_month,
    (SELECT array_agg(DISTINCT position) FROM project_reports prk WHERE prk.project_id = pr.project_id AND prk.keyword_id = pr.keyword_id AND date(prk.created_at) = date(current_date - interval '3' month)) as three_month,
    (SELECT array_agg(DISTINCT position) FROM project_reports prk WHERE prk.project_id = pr.project_id AND prk.keyword_id = pr.keyword_id AND date(prk.created_at) = date(current_date - interval '6' month)) as six_month,
    (SELECT array_agg(DISTINCT position) FROM project_reports prk WHERE prk.project_id = pr.project_id AND prk.keyword_id = pr.keyword_id AND date(prk.created_at) = date(current_date - interval '12' month)) as last_year,
    date(pr.created_at)
FROM project_keywords pk
INNER JOIN keywords k ON pk.keyword_id = k.id
INNER JOIN project_reports pr USING (project_id, keyword_id)
GROUP BY k.name, date(pr.created_at), pr.project_id, pr.keyword_id


texts: MySQL Data Synchronisation from different Databases in C#

texts:
I am supposed to create a C# solution to compare the data (table, column and row) from two different databases and update the 2nd one as follows:
1. with just the rows that are updated recently (incase of duplicates)
2. check for new/updated column in 1st one and update/delete in 2nd accordingly or drop the table entirely and copy it from 1st one.
3. check for new/updated table in 1st one and update/delete in 2nd accordingly or drop the table entirely and copy it from 1st one.
So basically I have to synchronize the data between the two databases.

As of now, I have just got to connecting the database part. I am not sure how to proceed (should I be dumping the data from the database and checking or is there a simpler way?).!!!

Any help would be appreciated.

Thank you!


texts: Linked server - connection fail from remote computer (using ssms)

texts:
I'm facing issue with linked server on Microsoft SQL Server 2012 (SP4).
When I remotely connect to SQL server (where linked server is created) with ssms and try to use/test linked server connection (e.g. Test Connection), I get the error

  Msg 18456, Level 14, State 1, Line 14, Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

But, when I log locally to SQL server I can access linked server with no problems. I'm using windows authentication.

Here is my configuration:
A (my workstation, ssms client)
B middle SQL server (linked server definition, pointing to C)
C target SQL server

So, the problem only appears when I'm connecting from A (ssms) to B and from here try to access SQL server on C via linked server. Locally, on B, the linked server is accessible corrrectly.
It seems like I'm facing double hop problem, but since my knowledge on this topic is (yet) not deep enough, I would like to ask for some help with diagnosing the issue.

Any help would be really appreciated!
Regards,
Domen

The linked server is configured as follows:
Connections are made using login's current context.

EXEC master.dbo.sp_addlinkedserver @server = N'linkedservername', @srvproduct=N'SQL_SERVER', @provider=N'SQLNCLI11', @datasrc=N'MY_SERVER_C'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'linkedservername',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
EXEC master.dbo.sp_serveroption @server=N'linkedservername', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'linkedservername', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'linkedservername', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'linkedservername', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'linkedservername', @optname=N'rpc', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'linkedservername', @optname=N'rpc out', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'linkedservername', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'linkedservername', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'linkedservername', @optname=N'collation name', @optvalue=NULL
EXEC master.dbo.sp_serveroption @server=N'linkedservername', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'linkedservername', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'linkedservername', @optname=N'use remote collation', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'linkedservername', @optname=N'remote proc transaction promotion', @optvalue=N'true'


texts: Minimum permission to execute sp_changedbowner SP

texts:
Is db_owner permission sufficient to execute sp_changedbowner?

I'm looking to change the database owner of a RDS SQL instance. I'm the owner of the DB, and still couldn't execute sp_changedbowner as master user.

I would like to know what the minimum permissions are to execute sp_changedbowner or ALTER AUTHORIZATION commands.


texts: I was earlier using MAMP and Now switched to MAMP PRO. I used to use MAMP mysql as root default user and having lots off database as that user. If i start MAMP and go to terminal with mysql command and do show databases; then it show me all databases. But if i start MAMP PRO and goto terminal and do show databases; then it show me only 4 databases mysql, information_schema, performance_schema and one db which is common for both MAMP and MAMP PRO. How can i use those existing DB on MAMP under MAMP PRO.


texts: Import Data from 48 GB csv File to SQL Server

texts:
I am importing huge data file of size around 48 gb using SQL Server default Import Tool. It keeps on executing fine for app. 13000000 Row insertions but after it the Task Fails with following error.
I cant open the csv as its damn huge neither can i move row by row in it and analyze the stats.
I am really confused how to handle this.


texts: Can SQL Server's crash resilience be improved?

texts:
We have PCs running SQL Server (2008 SP4 and 2016 SP1) which regularly lose power. Obviously, this sometimes leads to (index) corruption of the SQL Server database, which we need to restore afterwards.

I am aware that SQL Server is not designed for such scenarios and the correct solution is to fix the cause of the power loss (more on that below, if you are curious). Nevertheless, are there any tuning options in SQL Server that I can set to reduce the risk of database corruption on power loss?

Background: The "PC" is a Windows tablet mounted on a forklift. When the user turns off the forklift, the tablet loses power. We have tried to teach the users to properly shut down Windows before turning off the forklift, but failed (probably because just turning it off "works" most of the time). We are also currently investigating other options, such as adding a UPS which signals the tablet to shut down on power loss.


texts: Differential backup to a specific date in history

texts:
Is that possible to make backup to any specific date in the past in SQL Server?

For example I have full db backup for 15:30, then at 17:30 there were unintended actions on db: is there a way to backup changes made from 15:30 to 16:30?


texts: How to avoid frequently needing to re-grant permission in Hue?

texts:
My organization has a cluster/environment with the following:

Component   Version
Hue             2.5.1-695
HDP             2.1.2
Hadoop          2.4.0
Pig             0.12.1
Hive-Hcatalog   0.13.0
Oozie           4.0.0
Ambari          1.5.1
HBase           0.98.0
Knox            0.4.0
Storm           0.9.1
Falcon          0.5.0

data from Hive is queried by R programmers in the R language via ODBC (specially, RODBC).

Prior to an upgrade several months ago, the programmers simply entered their connection/authentication strings in their scripts and wrote queries within the R scripts to be run programmatically over ODBC.

However, since the upgrade, anytime a table is refreshed/recreated, it's necessary for the users to (re-) grant themselves permission to select via a query in Hue, such as:

grant select on table my_table to user kmitnick

Then queries over ODBC work as expected until the next time the table is refreshed.

This has caused a great number of automated/scheduled scripts to "break". Note that the exact same query can be run by the same within Hue and no grant select statement is needed.

Could someone help me understand the cause and/or find a work-around to avoid the need for these grant select statements.


texts: Postgres 12: declare a function as constant or immutable ignoring parameters?

texts:
In a trigger function I want to conditionally do pg_notify() calls, depending on an application setup option. In order to do that I created a wrapper notify_conditionally(). If the application is started with the notifications disabled, a second SQL function will create this wrapper that doesn't do anything and is defined as IMMUTABLE:

CREATE OR REPLACE FUNCTION notify_conditionally(channel text, payload text) RETURNS int
LANGUAGE plpgsql IMMUTABLE AS
$inner$
BEGIN
    PERFORM 1 WHERE 1 = 0;
    RETURN 0;
END;
$inner$;

If notifications are enabled, this function is defined as a call to pg_notify() with the parameters of the function:

CREATE OR REPLACE FUNCTION notify_conditionally(channel text, payload text) RETURNS int
LANGUAGE plpgsql STABLE AS
$inner$
BEGIN
    PERFORM pg_notify(channel, payload);
    RETURN 0;
END;
$inner$;

Since the parameters to conditionally_notify() (and hence pg_notify()) change with every call, I suppose the IMMUTABLE keyword doesn't help much in terms of result value caching (in fact, it might even be counter-productive?). Therefore I am wondering is there a way that I can define this function as "constant" and have the planner be clever enough to not call this function or only call it once and reuse the first result for all other calls? This would basically be like an IMMUTABLE that ignores all parameters. This would be useful to avoid calling this function if notifications are disabled. On bigger queries this makes a difference from ~500ms in my case and ideally I could lower this in the case of disabled notifications.

While it is a possibility to also update the code that uses notify_conditionally() function dynamically based on whether notifications are enabled and in case they aren't just not use notify_conditionally(), I would prefer to not do this. The reason is that this happens in a rather large trigger function that currently should only be changed through a full migration.


texts:
I am using Ola Hallengrens DatabaseBackup script. I am storing my backups in Azure Blob storage using the @url parameter. Everything is working fine, however I am looking for a way to set a retention policy on those backups - currently I assume they live there forever until I manually delete them. I tried using the @CleanupTime parameter but got the following error

The value for the parameter @CleanupTime is not supported

Is there any way I am able to set this?

Edit: SQL being run for the backup

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d DBATools -Q \
"EXECUTE [dbo].[DatabaseBackup] \
@Databases = 'USER_DATABASES', \
@BackupType = 'LOG', \
@Verify = 'Y',  \
@Compress = 'Y', \
@CheckSum = 'Y', \
@LogToTable = 'Y', \
@ChangeBackupType='Y', \
@url='https://example.blob.core.windows.net/backups', \
Credential='AzureBlobBackups'" -b


texts:
I'm running a MySQL server with a couple of customer databases, some of which are quite large. We are doing a complete mysql dump once a day. It takes about half an hour to complete.

During this time, every request to the MySQL server gets a ridiculously slow response (sometimes around 1 s instead of something like 10 ms). Even though there are no timeout errors, I would love to see the queries being completed in a decent amount of time, so customer websites are not slowed down by hanging MySQL queries.

I researched a lot and learned about data replication, LVM snapshots, various mysqldump flag options and other work-arounds, but nothing did really improve the situation.

Is there any option to set a query priority in MySQL?

If there was a way to define query priorities, I could give the backup process a lower priority to be sure all the other queries are executed faster. The mysqldump process could be temporarily paused (SIGTSTP) when it's challenged by another request and continued (SIGCONT) when the query is finished. However, I couldn't find a feature like this in MySQL natively.

Giving the mysqldump process a higher nice value doesn't work, because it just increases the time both, the backup process and the table locks will be present. (I'm locking per-table.) Also, limiting IO write rates just leads to longer pain.

Are there any solutions I missed, other than LVM and replication?


texts: Replication of MySQL databases, but only some of the data

texts:
I need to set up a system to do basically a partial backup/replication of a couple of MySQL databases (actually Percona XtraDB 5.5, all tables are InnoDB).  There are a few hundred tables in one of the databases and a handful of tables in the other.

Some tables should be replicated in full, while others should only have certain records replicated.  Basically the data belongs to different "users", and I only want a backup of the data related to one of the "users" (including any data that is not specific to a particular user).

So I don't think that any of the normal replication options would help with this, but please correct me if I'm wrong.

I think I'll have to write a script that exports the relevant rows from the relevant tables periodically and then imports them into the "slave" database.  The problem with this is that I'd like to do this once per day, and I would like to do essentially an incremental export instead of exporting everything every day.

Obviously I can't just keep track of the primary keys of the records I have exported, because older records can be updated after the daily export has run.  Maybe I could create triggers on every table to add a timestamp to every modified row or clear an "exported" field, but I suspect adding triggers to hundreds of tables might impact the performance of the system.

So is there some other way I can find out which records have been modified in the last 24 hours so that I can copy the relevant ones again?

Or is there another solution to this problem?


texts:
I have this simple query that finds all transactions from accounts belonging to a specific login, with some extra conditions on the transactions.

SELECT t.id, t.date, t.amount, t.description FROM transaction t
INNER JOIN account ac ON t.account = ac.id AND ac.login_id = ${loginId}
WHERE t.processed = false AND t.updated_by_user = false
AND t.category = 'uncategorized' ;

Will this query perform faster with a subquery on accounts, for example:

SELECT t.id, t.date, t.amount, t.description FROM transaction t
INNER JOIN (SELECT id FROM account WHERE login_id = ${loginId}) ac ON t.account = ac.id
WHERE t.processed = false AND t.updated_by_user = false
AND t.category = 'uncategorized' ;

I'd appreciate some insightful comments on this, thanks in advance!


texts:
I need to check the string with the column if the column contains that string or not.

example:String 'how to make pizza'  and the column if has 'pizza' i need to fetch that..
like

SELECT * FROM event WHERE ('%how to make pizza%' LIKE EventName);

any idea plz..


texts: At what point is a (disk-based) table removed from memory after data is read from it?

texts:
Are (disk-based, so no Hekaton) tables persisted in memory for longer than the lifespan of the query that is reading data from them?

If so, what determines how long they stay in memory for?

Is there a way to manage how long they persist in memory for?


texts:
I would like to combine these queries:

UPDATE T1
SET Opening_date = T2.Opening_date
FROM FactTransaction T1
INNER JOIN DimStore T2 ON T1.cod_Store = T2.Cod_StoreKey

and

UPDATE FactTransaction
SET ORDINAL = DATEDIFF(DAY, Opening_Date, DATE)

In order to avoid insert the field Opening_date in my fact table. I don't know how to do it just with a query.


texts: Trying to reduce number of table scans when querying on the same column but different values

texts:
I'm having a mental block. I'm using a sample MySQL customer database...

http://www.mysqltutorial.org/mysql-sample-database.aspx

And would like to query for any customers that bought two products (product codes S18_1589 and S24_1628). I wrote this query...

SELECT customerNumber
FROM orders o
WHERE customerNumber IN (SELECT customerNumber
                         FROM orders o
                         JOIN orderdetails od
                         ON o.orderNumber = od.orderNumber
                         WHERE od.productCode = 'S18_1589')
AND customerNumber IN (SELECT customerNumber
                         FROM orders o
                         JOIN orderdetails od
                         ON o.orderNumber = od.orderNumber
                         WHERE od.productCode = 'S24_1628')
GROUP BY customerNumber
ORDER BY customerNumber;

That gets the results that I want but I'd like to do this without using two sub queries if possible. The customers can buy any other products but they need to have purchased the two mentioned.

In order to try to make the query more efficient, I changed it to this...

SELECT customerNumber
FROM orders o
JOIN orderdetails od
ON o.orderNumber = od.orderNumber
WHERE od.productCode IN ('S24_1628', 'S18_1589')
GROUP BY customerNumber
HAVING COUNT(*) >= 2
ORDER BY customerNumber;

which gives me the same result as the first query but I think it's just a coincidence. I believe the query will also include customers that have purchased only one of the products but 2 or more times.

Anyone know a better way / more efficient way of handling?

NOTE: I was given this question and was told my query was not performant because of the two subqueries but no further feedback was given. It's a different dataset / schema than what I'm using now but size is similar and what I'm trying to query is the same type of question.


texts: Select all rows with multiple conditions in another table


texts: Get value of two instances of a secondary table

texts:
I have two tables, users and permissions.  Users have a username.  Permissions have a name (e.g. cards, pages, players) and a type (e.g. read only, read/write) and a foreign key relationship to users.  Both tables have an basic integer primary key.

For various reasons I would like to query all user to see the status of their cards permission and pages permission at the same time.  My ideal output would be something like...

|------------------------------------------------|
| Username | Cards Permission | Pages Permission |
|------------------------------------------------|
|   Bob    |   Read / Write   |   Read Only      |

I have already thought of two ways I can do this but neither is ideal.

First, I can query twice, each time collecting User.Id, User.Username, Permission.Type.  The first for cards permission and the second for pages permission.  I can then correlate the two queries in the application.

SELECT u.Id, u.Username, p.Type
  FROM User u
  INNER JOIN Permission p ON p.UserId = u.Id
  WHERE p.Name = '?';

Second, I can query once, collecting User.Id, User.Username, Permission.Name, Permission.Type.  The query would join the permissions table and take all permissions whose name is Cards or Pages (essentially I'd get 2 times as many records, each User.Id would show up twice).  I could then correlate the results in the application.

SELECT u.Id, u.Username, p.Name, p.Type
  FROM User u
  INNER JOIN Permission p ON p.UserId = u.Id
  WHERE p.Name = 'cards' or p.Name = 'pages';

Is there any query I can use that would give me the output I desire?  I'm asking mainly for my own edification.  If I JOIN the permissions table twice I end up with a cross product and run out of memory (there are 1,000,000 users and 10,000,000 permissions).

UPDATE: Detailed Schema

User
----
Id bigint not null identity primary key
Username varchar(64) not null

Permission
----------
Id bigint not null identity primary key
UserId bigint not null foreign key
Name varchar(64) not null
Type int not null
-Index non-clustered non-unique Id, UserId, Name


texts: Filling in date holes in grouped-by-date SQL data

texts:
The actual problem involves far more data and joins, but I've created a small sample to demonstrate the issue:

-- create example table
DROP TABLE dbo.EventRecords
GO
CREATE TABLE dbo.EventRecords
    (
    EventDate datetime NOT NULL,
    EventCount int NOT NULL
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.EventRecords ADD CONSTRAINT
    PK_EventRecords PRIMARY KEY CLUSTERED
    (
    EventDate,
    EventCount
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
-- put in some random data for example
DECLARE @Counter INT=0
WHILE (@Counter<1000)
BEGIN
    DECLARE @SemiRandomCount1 INT=@Counter*589043%23
    DECLARE @SemiRandomCount2 INT=@Counter*85907%7
    IF @SemiRandomCount1>0 AND @Counter%7<>0    -- leave some dates empty
    BEGIN
        INSERT INTO dbo.EventRecords(EventDate,EventCount)
        VALUES (DATEADD(day,@Counter,'2013-01-01'),@SemiRandomCount1)
        PRINT CAST(@SemiRandomCount2 AS VARCHAR(MAX))
        IF @SemiRandomCount2>0 AND @Counter%2=0 -- some dates have multiple entries
            INSERT INTO dbo.EventRecords(EventDate,EventCount)
            VALUES (DATEADD(day,@Counter,'2013-01-01'),@SemiRandomCount2)
    END
    SET @Counter=@Counter+1
END
--SELECT * FROM dbo.EventRecords

So, some dates have multiple entries, some have none.  I need to get results for reporting that contain every date in a specified range, with the total counts for that date (zero if there were no counts for that date).  After much googling and experimentation, I found a very clever way to generate sequences on the fly and built this function from it.  These sequences can be used to build a date sequence table on the fly which can then be used to join the EventRecords table and group by date with no holes:

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE name='GetSequence')
   EXECUTE sp_executesql N'CREATE FUNCTION GetSequence() RETURNS @Table TABLE (Value SMALLINT NOT NULL) AS BEGIN     RETURN END'
GO

ALTER FUNCTION [dbo].[GetSequence](@StartInclusive INT, @EndExclusive INT)
RETURNS @Sequence TABLE
(
    Value BIGINT NOT NULL
)
AS
BEGIN
    INSERT @Sequence
    SELECT Value=@StartInclusive+n-1
    FROM (SELECT ROW_NUMBER() OVER (ORDER BY o1.n)
        FROM (SELECT n=ROW_NUMBER() OVER (ORDER BY object_id) FROM sys.objects WITH (NOLOCK)) o1
        CROSS JOIN (SELECT n=ROW_NUMBER() OVER (ORDER BY object_id) FROM sys.objects WITH (NOLOCK)) o2
        CROSS JOIN (SELECT n=ROW_NUMBER() OVER (ORDER BY object_id) FROM sys.objects WITH (NOLOCK)) o3
        CROSS JOIN (SELECT n=ROW_NUMBER() OVER (ORDER BY object_id) FROM sys.objects WITH (NOLOCK)) o4
        CROSS JOIN (SELECT n=ROW_NUMBER() OVER (ORDER BY object_id) FROM sys.objects WITH (NOLOCK)) o5
        CROSS JOIN (SELECT n=ROW_NUMBER() OVER (ORDER BY object_id) FROM sys.objects WITH (NOLOCK)) o6
        ) D (n)
    WHERE n<=@EndExclusive-@StartInclusive
    RETURN
END
GO

Here are sample queries:

DECLARE @StartDate DATE='2013-01-01'
DECLARE @EndDate DATE='2015-01-01'
-- query with holes: not what I need
SELECT [EventDate], [TotalEventCount]=ISNULL(SUM(EventCount),0)
FROM dbo.EventRecords
GROUP BY [EventDate]
-- query with date holes filled in: this is what I need
SELECT
    [EventDate]=DATEADD(day,s.Value,@StartDate),
    [TotalEventCount]=ISNULL(SUM(EventCount),0)
FROM [dbo].[GetSequence](0,DATEDIFF(day,@StartDate,@EndDate)) s
LEFT JOIN dbo.EventRecords c
    ON DATEDIFF(day,@StartDate, EventDate)=s.Value
GROUP BY s.Value

So, my question is: is there a better (simpler or faster) way to get sequences, or a better way to solve this problem in SQL?


texts:
I'm getting these errors when testing a linked server from SQL Server 2012 to SSAS 2012 (both servers are Windows 2012 and clustered)

"An error was encountered in the transport layer" and "The peer prematurely closed the connection"

I've spent 2 days on this now and has been swung towards Kerberos. I've enabled Kerberos in AD for all of the computer names involved which are host names, SQl cluster names and windows cluster name and have SETSPN for the SQL Server cluster name and SQL Server cluster name FQDN against the service account used.

The same service account is running SQL and SSAS and is a SysAdmin on each in security. The linked server works fine if I RDP onto Server1 and test the linked server to Server2 but it won't work from anywhere else - there are no firewalls in between these servers and windows firewall is disabled on both.

To create the linked server on Server1 (SQL Server) I have followed all links on other msdn forum posts and this is what I used in order to connect to Server2 (SSAS)

EXEC master.dbo.sp_addlinkedserver
@server = N'LinkedServerTest',
@srvproduct='',
@provider=N'MSOLAP',
@datasrc=N'Server2',
@catalog=N'TestCUBE'

EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'Server2',
@useself=N'True',
@locallogin=N'Domain\ServiceAccount',
@rmtuser=NULL,
@rmtpassword=NULL

Really hit a brick wall with this now any help is appreciated. Just to add SQL browser is running on both and both servers are using default instances, Server1 is SQL Server etc and Server2 is literally SSAS installed, nothing else.

Just to add to this I've installed the MSOLAP.5 provider as it's part of the SQL Server 2012 SP1 feature pack and it still doesn't change my situation and we use the same service account for Server1 (SQL Server Service) and Server2 (SSAS Service) here


texts: Is backing a website by a SQL Server 2012 OLAP cube considered reasonable?

texts:
I have been tasked with architecting a solution for a large retail chain. They want to allow each of its 1.2 million customers to log on to a web site to see the distribution of recent purchases (current month, previous month, year-to-date) over about 50 categories.
Data will be updated once every day.

I am thinking of putting up a SQL Server 2012 based OLAP cube and letting the website query this cube directly, leveraging features like proactive caching. However, being a developer at heart, I have next to no experience with the analysis services parts of SQL Server, so am quite concerned about the performance of this solution.

Does connecting a web site directly to an OLAP cube sound like a feasible solution? Do such systems react to the load from multiple users roughly like a SQL Server, making this a reasonable solution, or do they act completely differently?

I don't expect users to check their status very often and I will of course be using caching on the webserver etc.


texts: Programmatic way to rewrite a query with the view definitions as sub query?

texts:
Wondering if anyone has found or knows of a way to programmatically re-write a query to use the view definition as a subquery?
For example, let's say I have a view:
CREATE VIEW View1
AS
select * from table1
go

I want to be able to take the query:
select * from View1

And have it automatically rewritten as
select *
from (
  select * from table1
) as View1

This is a simple example, but I'd like to be able to do this for more complex queries, for example, if a view references another view, or there are multiple views joined together.  Rather than manually looking at the definitions of all the views and rewriting it manually, I was hoping to come up with a way to do this programatically some how.  I could probably figure something out on my own, but I just wanted to ask first in case someone knows of something that already does this so I don't have to recreate the wheel.


texts: Has somebody got a function that will extend hashbytes to strings of arbitrary length?

texts:
I want to apply it to procedure definitions.

My basic idea is to split the string of type nvarchar(max) into chunks of nvachar(4000) and concatenate the results of HashBytes('MD5',chunk).

I can't imagine that this is not done yet.


texts:
I have 400,000 records I need to partition. The records will increase 100,000 per year and those new records might need to have 1000 new partitions added.

Here are some of my concerns:

How many partitions are too many
Is having small partitions bad (could have less than 150 records per partition)
Large partitions will be 10,000 or more records
partitions are created by field, not a date
new records could be added daily to the larger partitions but the smaller partitions might be monthly
possibly need to separate partitioned data legally (some cases but not all)

Example:

Let's say 10,000 out of 100,000 will need to be broken into partitions of 100 records, so 1000 partitions will be created and 9 additional partitions will be created with 10,000 records each, for a total of 1009 partitions.

Questions:

is this approach going to work
is this the best approach, if not what are some other ideas

Notes:

Most records will fall into the larger partitions


texts: Mysterious conditional where clause index choices

texts:
Have you got an explanation, why query optimizer is choosing different indexes and modes in this example?

/* crete objects and data for testing */

-- table
CREATE TABLE #Test (
    ID INT IDENTITY PRIMARY KEY
    ,CustNo INT NULL
    ,CustNo2 INT NULL
    );

-- populate with data
WITH
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
  L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
  L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
  L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
  L5   AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
INSERT INTO #Test (CustNo)
SELECT TOP (1000) n FROM Nums ORDER BY n;

-- index on CustNo
CREATE NONCLUSTERED INDEX IX__#Test__CustNo ON #Test (CustNo ASC);

/* running test */

-- variables
DECLARE @Step INT = 1;
DECLARE @FindNo INT = 5;

-- #1 - uses CX index scan
SELECT CustNo
FROM #Test
WHERE (@Step = 1 AND CustNo = @FindNo)
    OR (@Step = 2 AND CustNo2 = @FindNo);

-- #2 - uses NCX index seek
SELECT CustNo
FROM #Test
WHERE (@Step = 1 AND CustNo = @FindNo)
    OR (@Step = 2 AND CustNo2 = @FindNo)
OPTION (RECOMPILE);

-- #3 - uses NCX index seek
IF @Step = 1
    SELECT CustNo
    FROM #Test
    WHERE CustNo = @FindNo;


texts: heroku postgres sql changes not reflected in db

texts:
I wrote this program

import psycopg2

try:
    #connect to db

    cursor = connection.cursor()

except Exception as e:
    raise e
else:
    print("connection Success")
cmd = ""
while(True):
    cmd= input("Enter cmd")
    if cmd == "CLOSE":
        break
    try:

        cursor.execute(cmd)
        pass
    except Exception as e:
        print("Err")
        print(e)
        pass
    else:
        record = ""
        try:
            record = cursor.fetchall()
        except Exception as e:

            print(e)

        print("Success ")
        print(record)

        pass
    finally:
        pass

cursor.close()
connection.close()
print("all close")

Weirdly, it worked fine yesterday for sometime.
However now changes are not reflected in db (when checked through heroku dataclip) but can be seen through python app before closing the session.Only .
I an sure I haven't crossed any limit of the hobby dev plan.

Curious enough the primary key still increments.
Output example-

this query(insert into info(name,nickname,ismetro) values('abc', 'xyx','n') worked a while ago.


texts: What is better to use for this case, a CTE or a table variable?

texts:
I sometimes need to write t-SQL code to send emails per row based on some conditional checks on some tables. So because the original table does not have an identity or some such column to track rows, I have to insert the data i want into a table variable, something like this:

DECLARE @i int
DECLARE @rowCount int
DECLARE @body nvarchar(max)
DECLARE @tableVar1 TABLE(rowid int identity(1,1), col1 nvarchar(10), col2 nvarchar(10), col3 nvarchar(30))

INSERT INTO @tableVar1(col1, col2, col3)
SELECT col1,col2,col3
FROM MyTable
WHERE (some condition(s))

SET @rowCount = @@ROWCOUNT

SET @i = 1

WHILE (i < @rowCount)
BEGIN

SET  @body = (SELECT col1 from @tableVar1 where rowid = @i) + ' and ' + (SELECT col2 from @tableVar1 where rowid = @i) + ' and ' + (SELECT col3 FROM @tableVar1 where rowid = @i) + ' are the columns.'
EXEC msdb.dbo.sp_send_dbmail @profile_name = @profileName, @recipients = 'recipientlist', @subject = 'someSubject', @body = @body

SET @i = @i + 1

END

Now, I have two questions, first is, when there is a lot of resultant data from the table, it becomes a mess of several dozens of separate SELECTS from the table variables just to retrieve data from say, one column, then more SELECTS from another column, etc. Is there another way for such a scenario to avoid a lot of SELECTs?

Second, would a CTE be better vs a Table Variable when querying hundreds of thousands of rows and performing calculations per row? I don't fully understand CTEs and have not really used them. For this sort of a thing, I only use the kind of solution I have written above.


texts: Replication is missing a specific set of rows on subscriber

texts:
So I've got two servers, we'll call them Server A and Server B.

Server A has a table, Table 1, which gets replicated to Server B.

Server B has a table, Table 2, which gets replicated to Server A.

All replication is transactional and pushed from the publisher continuously.

On Server B, there is a trigger on Table 1 which will ultimately create a record in Table 2.

Other various processes also create additional records in Table 2. All of the records in Table 2 are supposed to get replicated back to Server A.

In short, the data from Server A, Table 1 is supposed to make a round trip back to Server A in table 2 along with other records. However only the 'other records' are coming back. None of the data which originated on Server A is coming through, even though it's been successfully inserted into Table 2 on Server B.

Any ideas?

Additional info:

When I generate a new snapshot all those records that didn't replicate are included in the snapshot and do get replicated over. My suspicions lie in what's happening behind the scenes when the trigger fired by replication inserts the data into Table 2 and perhaps something is preventing those records from replicating over.

The trigger looks something like:

Create Trigger [dbo].[trg_INS_TableA]
For Insert
As

Declare @<Various Parameters>

Select @<Various Parameters> = <Record Data>
From inserted

Declare @RC Int
Exec @RC = [MyDB].[dbo].[up_INS_TableB] @<Various Parameters>

And up_INS_TableB transforms and inserts the Table A data into Table B


texts:
I have a slow log generated by MySQL 5.1.71-log, that I've copied onto another machine. It appears I'm not getting any results from pt-query-digest.

$> pt-query-digest slow.log

# 2.1s user time, 910ms system time, 506.36M rss, 687.04M vsz
# Current date: Fri Sep 12 10:39:50 2014
# Hostname: util.ultralinq.net
# Files: slow.log
# Overall: 0 total, 1 unique, 0 QPS, 0x concurrency ______________________
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Query size        66.26M  66.26M  66.26M  66.26M  66.26M       0  66.26M

# Profile
# Rank        Query ID    Response time Calls       R/Call      V/M   Item
# =========== =========== =========== =========== =========== ===== ======

$> head -n 1 slow.log
"2014-07-10 17:02:48","XXX[XXX] @  [A.B.C.D.]","00:00:18","00:00:00",4,1817425,"echo",0,0,3,"(SELECT 'readCount' AS var, COUNT(id) AS data FROM study\n                WHERE ctime > '2014-07-09 21:02:29' AND status='read') union (SELECT 'unreadCount' AS var, COUNT(id) AS data FROM study\n                WHERE ctime > '2014-07-09 21:02:29' AND status in ('unread', 'upload')) union (SELECT 'avgStudySize' AS var, SUM(size)/COUNT(distinct study_id) AS data FROM echo\n                WHERE ctime > '2014-07-09 21:02:29') union (SELECT 'procDelay' AS var, TIME_TO_SEC(TIMEDIFF('2014-07-09 21:02:29', MIN(ctime))) AS data FROM echo\n                WHERE ctime > '2014-07-09 21:02:29' and post_process_status = 'unprocessed')"

$> wc -l slow.log
144973 slow.log

$> pt-query-digest --version
pt-query-digest 2.2.10

Why am I not getting more interesting results?


texts: Write one or more triggers to manage the grade attribute of new Highschoolers

texts:
If the inserted tuple has a value less than 9 or greater than 12, change the value to NULL. On the other hand, if the inserted tuple has a null value for grade, change it to 9. ```

CREATE TRIGGER gradeTrigger1
AFTER INSERT ON highschooler
WHEN new.grade < 9 OR new.grade > 12
BEGIN
    UPDATE highschooler SET grade=NULL WHERE id=new.id;
END


texts: ORA-01113: file # needs media recovery ORA-01110

texts:
I have mistakenly added datafile to the tablespace... And was trying to drop it. I run the following:

alter database datafile 'datafile_name'  OFFLINE;

now when I run

alter database datafile 'datafile_name'  ONLINE;

it shows

ORA-01113: file 30 needs media recovery
ORA-01110: data file 30: 'datafile_name'

also status of the file is RECOVER... What can I do? I cant restart database....


texts:
I have a database with some relatively small tables and one big table with about 10 million rows (11 columns) that I would like to optimize its performance. The table looks like this:

Id: (int 11, primary key)
Codigo: (longtext)
Act_mas: (double)
Act_menos: (double)
Ind_mas: (double)
Ind_menos: (double)
Cap_mas: (double)
Cap_menos: (double)
Mensaje: (longtext)
Fecha: (datetime)
SuministrosId (int 11, this is a foreign key of a table with 62 rows, 21 columns that has another 2 foreign key, one with 44 rows and 11 columns and the other with 2 rows and 2 columns)

I guess I could change the datatype of Codigo and Mensaje to something like VARCHAR(10), since normally the value has less than 10 characters and probably LONGTEXT is an overkill.

I could also change column datatype, from Act_mas to Cap_menos, to INT, since all the values should be smaller than 1.000.000 (at the moment the biggest value is 795.600, and it's very strange to have such a value).

The database and the table is using 'InnoDB' engine.

Actually a query like SELECT * FROM table WHERE SuministrosId = 4 and Fecha >= "2015-01-01" takes about 3-5 seconds to load. Maybe its not too much but I think it could be lot less.
I tried to change the datatype of some columns but it was terribly slow and I had to stop it, should I give another try? Also I added an index to SuministrosId but it seemed to be the same.

If other data is needed feel free to ask. Thank you in advance! (And sorry for my English :c)

EDIT

Computer info:

CPU: Intel(R) Xeon(R) CPU E5-2609 v4 @ 1.70 GHz

RAM: 32 GB

OS: Windows Server 2012 R2 Standard

MySQL Version: 5.7.11

The database is used by an external java program that typically uses queries like the described above: SELECT * FROM consumos WHERE SuministrosId = 4 and Fecha >= "2015-01-01", this is the most used query since it's the "core" for the job done here. Some other queries are used to list clients like: SELECT * FROM clientes, or SELECT * FROM suministros. But those tables are really small and they are not a problem.

SHOW CREATE TABLE for the big one:

CREATE TABLE `consumos` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`Codigo` longtext NOT NULL,
`Act_mas` double NOT NULL,
`Act_menos` double NOT NULL,
`Ind_mas` double NOT NULL,
`Ind_menos` double NOT NULL,
`Cap_mas` double NOT NULL,
`Cap_menos` double NOT NULL,
`Mensaje` longtext NOT NULL,
`Fecha` datetime NOT NULL,
`SuministrosId` int(11) NOT NULL,
PRIMARY KEY (`Id`),
KEY `IX_FK_Contadorsuministrosset` (`SuministrosId`),
KEY `Fecha` (`Fecha`),
CONSTRAINT `FK_Contadorsuministrosset` FOREIGN KEY (`SuministrosId`) REFERENCES `suministros` (`Id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=13002432 DEFAULT CHARSET=latin1

Query explain:

Profile:


texts: Why one database is so much slower than other?

texts:
The same query on postgresql takes 9 min on one database, on other only 5 sec.

I use the same postmaser, the same user. I connect directly with psql.

There seems to be no differences in database settings:

psql dbone -c 'SELECT * FROM pg_settings;' > pg_settings_dbone.txt
psql dbtwo -c 'SELECT * FROM pg_settings;' > pg_settings_dbtwo.txt
diff pg_settings_dbone.txt pg_settings_dbtwo.txt

EIDT:

Databases should be the same (scheduled copy)

query

explain analyze select clinic.code_8,
    (select count(distinct visit.id)
            from patients lpatient
            join visits_patients vp on (vp.hq_id = lpatient.hq_id and vp.patient_queue_id = lpatient.ext_id and vp.status='N' and vp.state <> 'C')
            join visits visit on (visit.hq_id = vp.hq_id and visit.ext_id = vp.visit_id and visit.state <> 'C' and date_trunc('day', visit.start) = date_trunc('day', slot.slot_start))
            join timetables vtimetable on (vtimetable.hq_id = visit.hq_id and vtimetable.ext_id = visit.timetable_id)
            join clinics vclinic on (vclinic.hq_id = vtimetable.hq_id and vclinic.ext_id = vtimetable.clinic_id and vclinic.code_8 = clinic.code_8)
        where ((lpatient.hq_id, lpatient.ext_id) in (select hq_id, ext_id from patient_links where patient_id = patient.id))),
    (select count(distinct visit.id)
            from patients lpatient
            join visits_patients vp on (vp.hq_id = lpatient.hq_id and vp.patient_queue_id = lpatient.ext_id and vp.status='N' and vp.state <> 'C')
            join visits visit on (visit.hq_id = vp.hq_id and visit.ext_id = vp.visit_id and visit.state='N')
            join timetables vtimetable on (vtimetable.hq_id = visit.hq_id and vtimetable.ext_id = visit.timetable_id)
            join clinics vclinic on (vclinic.hq_id = vtimetable.hq_id and vclinic.ext_id = vtimetable.clinic_id and vclinic.code_8 = clinic.code_8)
        where (lpatient.hq_id, lpatient.ext_id) in (select hq_id, ext_id from patient_links where patient_id = patient.id)),
        stype.allow_series
from patients patient,
timetable_slots slot
    join visit_types stype on (stype.hq_id = slot.hq_id and stype.ext_id = slot.visit_type_id and stype.status='N')
    join timetables timetable on (timetable.hq_id = slot.hq_id and timetable.ext_id = slot.timetable_id)
    join clinics clinic on (clinic.hq_id = timetable.hq_id and clinic.ext_id = timetable.clinic_id)
where patient.hq_id = 1 and patient.ext_id = 234223 and slot.hq_id = 3 and slot.ext_id = 313245

fast query http://explain.depesz.com/s/jjC2

slow query http://explain.depesz.com/s/e26V

EDIT2:

Somehow indexes where corrupted on second db. Second db was copied without indexes?