texts: RESTORE failing with OS error 32, file being used by another process

texts:
I am writing a set of procedures for (partially) automating DB deployment for a customer, which mostly works fine, but sometimes a RESTORE command is failing with the OS error 32, about a file being in use by another process (details below).

I have googled this extensively but have found little that applies to my specific case.  I suspect that there's something that I am overlooking, but I just can't seem to find it.

Here's the command:

RESTORE DATABASE [NBBC_Logistics] FROM DISK = '\\wpdboardq01\Shares\DbCopy\DevBackups\NBBC_Logistics_140916112310.bak'
    WITH FILE=1, NOUNLOAD, STATS=10,
     MOVE 'NBBC_Logistics' TO 'D:\MSSQL2K12\MSSQL11.MSSQLSERVER\MSSQL\DATA\NBBC_Logistics.mdf',
     MOVE 'NBBC_Logistics_log' TO 'D:\MSSQL2K12\MSSQL11.MSSQLSERVER\MSSQL\DATA\NBBC_Logistics_log.ldf',
     REPLACE
    ;

This results in the following error message:

Msg 3634, Level 16, State 1, Line 11
The operating system returned the error '32(The process cannot access the file because it is being used by another process.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'D:\MSSQL2K12\MSSQL11.DEV\MSSQL\DATA\NBBC_Logistics_log.ldf'.
Msg 3156, Level 16, State 8, Line 11
File 'NBCC_Logistics_Model2_log' cannot be restored to 'D:\MSSQL2K12\MSSQL11.DEV\MSSQL\DATA\NBBC_Logistics_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 11
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 11
RESTORE DATABASE is terminating abnormally.

Some things to note:

This only happens on some of the receiving SQL Instances most of them are executing very similar commands with no problem.
The instance that is failing is one where there are multiple SQL instances on the same box (DEV and QA) and it is trying to restore a DB backup from DEV into the QA version of the same database.
Other DBs in this same instance can execute their corresponding RESTORE command without any problems.
It may be relevant that the source logical file names (shown in the error) are different from the DBs existing ones (named in the command), however, I beleive that I have cases where this does work.
Also, note carefully, that the file path reported by the error is NOT what I am specifying in the MOVE, but rather the original file path (which does still have the original DB files in use by the DEV instance).

So it appears that it is trying to first RESTORE the DB files to their original path locations, and only then MOVE them to the path I tell it to.  That is in contrast to what the doc says it does, and obviously would be compeltely unworkable in general, as someone RESTOREing a DB copy has no control over where the original files were and could not gaurantee that such paths existed and were not already in use.

Any help would be greatly appreciated.

Just to head off some of the automatic responses that don't apply:

The target database is not currently in use by me or anyone else.
Nor does it show up in sp_lock
The command specifies REPLACE, it is supposed to overwrite the existing database
The files specified are not open by any other windows processes
however, the file mentioned in the error are open by the other Sql Instance on the box
the BAK file location on a share has nothing to do with it, copying it locally changes nothing, nor does renaming it.
the db files and paths specified are the correct ones for the target database


texts: SSIS Data Import from CSV to SQL Table Failing

texts:
I have been trying to import the data from CSV file to SQL Table. I have 7 columns which are:

Refresh Date - Db_Date
Report Date - DB_Date
Report Period - Four byte singled Int
Participated - Four byte singled Int
Organized - Four byte singled Int
Peert-To-Peer - Four byte singled Int

I am using Flat File Source --> Data Conversion (to convert the data in my csv to match the data type according to the above table) --> OLE DB Destination.

I am getting following errors:

[Data Conversion [2]] Error: Data conversion failed while converting column ""???Report Refresh Date"" (37) to column "Copy of "???Report Refresh Date"" (11).  The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
[Data Conversion [2]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "Data Conversion.Outputs[Data Conversion Output].Columns[Copy of "???Report Refresh Date"]" failed because error code 0xC020907F occurred, and the error row disposition on "Data Conversion.Outputs[Data Conversion Output].Columns[Copy of "???Report Refresh Date"]" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Data Conversion" (2) failed with error code 0xC0209029 while processing input "Data Conversion Input" (3). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.

I am also attaching the screenshot of how my CSV file looks like.

Any help will be helpful as I have a ton of data which needs to be load to SQL tables from CSV files.


texts: SQL server - Re. Filegroup / partitioning

texts:
Assuming VLDB is split into multiple file groups, each having its own file and on a separate disk.

Now let's say there is a disk crash bringing down one of the file groups - then will the other parts of the database be accessible?

Is it good practice to assign multiple disks to 1 filegroup OR create multiple file groups with independent disks?


texts:
In a MySQL 5.6 database I found one user 'foo'@'10.0.0.2' with ALL PRIVILEGES.

I also discovered an user 'foo'@'10.0.0.3' granted only USAGE privileges.  Does this second user definition make sense?

Thanks.

Edit: for the sake of clarity, the MySQL database is on a machine which is neither 10.0.0.2 nor 10.0.0.3.


texts:
I ran a LIST BACKUP SUMMARY on my system, the output is pasted below.

Did my backups fail?

I know TY is Type (B for Backup,right?), but what do LV and S mean?

RMAN> LIST BACKUP SUMMARY;

Lista di backup
===============
Key     TY LV S Device Type Ora di completamento #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
1       B  A  A DISK        04-DIC-16            1       1       NO         BACKUP-LIBRI
2       B  F  A DISK        04-DIC-16            1       1       NO         TAG20161204T
3       B  F  A DISK        04-DIC-16            1       1       NO         TAG20161204T
4       B  A  A DISK        04-DIC-16            1       1       NO         BACKUP-LIBRI


texts: What's the most efficient way to reclaim unused DB hard drive space?

texts:
Disclaimer: I'm not a DBA and am asking this question to help research a problem a colleague is having.

We have a database that unintentionally ballooned to 300+ GB. We have turned off the option that caused this in our application, but we want to purge the offending data (in only 1 table) and reclaim the space it was taking up on the hard drive. We have tried truncating the table and then shrinking it on a copy of the production database, but this process takes about a week.

Is there a quicker/better/more efficient way to do this? I have read that shrinking a database is a taboo among DBAs because it introduces index fragmentation.

We are using Microsoft SQL Server.

"Best way" translates to quickest and least likely to cause us issues in the future (like index fragmentation might). We are trying to make this as quick as possible so that our database won't be tied up for several days deleting data and shrinking files.


texts:
Oracle RAC 11.2 with asm storage, all type = EXTERN redundancy.

I've been cleaning up some tablespaces and shrinking datafiles back down to sizes that they're not likely to grow much larger than (processes no longer used had blown them up).

The sum of the bytes of datafiles from dba_data_files reflects this shrink, however, USED_MB in v$asm_diskgroup has not fallen and it doesn't match to what adding up the datafiles bytes is?

What would account for this difference?  We typically use v$asm_diskgroup to monitor if we're running low on space but this doesn't appear to be correct.

  SELECT name group_name ,
  type type ,
  total_mb total_mb ,
  (total_mb - free_mb) used_mb ,
  ROUND(
  (SELECT SUM(bytes)/1024/1024
  FROM dba_data_files
  WHERE file_name LIKE '+DATA02%'
  ),0) AS datafile_used
FROM v$asm_diskgroup w
WHERE name = 'DATA02'

GROUP_NAME  TYPE  TOTAL_MB  USED_MB  DATAFILE_USED
______________________________________
DATA02  EXTERN  542712  516902  506447


texts: If all variables are a subset of the candidate key, is the database design 5NF?

texts:
I have a table called LogMessages, which has the following columns:

Level

A numeric value which represents Trace, Debug, Info, Warning, Error or Fatal

Time

A UTC time

Message

Foreign key to a Messages table

Source

Foreign key to a Sources table

User

Foreign key to a Users table

From what I can see, all of these columns are a part of the candidate key; if any single value differs to an existing row, a new row can be created.

My question is, does this design comply to fifth normal form? I am unsure as some groups of data will be repeating, however I don't believe this violates 5NF? (correct me if I'm wrong)

Update

It is possible to create a new row in this table if any single one of the columns changes, e.g. It is possible to have the same message at a different level ('User does not exist' could be logged as a warning at one point in time and an error at a different time).


texts: "Error while processing statement" running a query finding unique values with multiple values in other rows

texts:
Is there anything wrong in the syntax of this query finding unique values with multiple values in other rows?
Select Distinct Ss.`cd`, Ss.`cd`
From nit.assrr As Ss
    Join (
       Select `cd`
       From nit.assrr
       Group By `cd`
       Having Count(Distinct `cd`) > 1
      ) As Y
On Ss.`cd` = Y.`cd`

I am getting this error:

[Cloudera]HiveJDBCDriver ERROR processing query/statement.
Error Code: 2, SQL state: Error while processing statement: FAILED:
Execution Error, return code 2 from
org.apache.hadoop.hive.ql.exec.mr.MapRedTask, Query:  .. my query


texts: When dedicated server process model should be used instead of shared process model?

texts: It looks like Oracle shared server process model is better than dedicated server process model. In a shared server configuration, client user processes connect to a dispatcher and it can support multiple client connections concurrently (source). Also, shared server can be configured for connection pooling and session multiplexing, so all these things should bring a huge performance boost. So, with these advantages, are there any cases when dedicated server process model should be used instead?


texts: Pull the latest Employee and Manager Comments Based on Modified date

texts:
I have a table Comments which is linked to the Plan table. Each time the user adds a comment on a specific plan, the Comments table is populated. This table stores the historical data. I need to pull out the latest record with the employee comments and the manager comments.

Comments Table schema is as shown below:

EmpFK | PlanFK | Comments | ModifiedDate
  60       7     empCmnt1   13-04-2016
  61       7     mgrCmnt1   14-04-2016
  60       7     empCmnt2   18-04-2016
  61       7     mgrCmnt2   19-04-2016
  60       8     empcmnt1   01-03-2016

Plan Table schema is as follows:

PlanPK |  PlanName
  7         Name1
  8         Name2

Employee Table Schema:

EmpPK | MgrPK  | EmpName
  60      61       user1
  61               mgr1

Here 60 is the empid and 61 is the mgrid. EmployeePK and the ManagerId column in the 'Employee' table gives the employee and manager details. Each employee will have only one manager. No higher lever consideration is needed in this scenario
Expected Result:

empid  |  mgrid  | EmpCmnt  |  MgrCmnt  | ModifiedDate | planFK
  60         61     empCmnt2    mgrCmnt2   19-04-2016       7
  60         61     empcmnt1               01-03-2016       8


texts:
I'm wondering if the negator clause of CREATE OPERATOR specifies how the negator can be interpreted, or how it will be interpreted. For example, if I have an operator >= and I set the negator to the operator <, will NOT(x >= y) always be interpreted as x < y, or does it just mean that it will be interpreted that way when it's advantageous.

I'm asking because I'd like to be able to be able to use NOT(x op y) in my queries as something other than the complement of x op y.  Is this possible and/or would it be playing with fire?

Concretely, I have a composite type with a subcolumn match_id and a subcolumn second.  I want to be able to use, for example, NOT(x >= y) to mean x.match_id = y.match_id AND x.second < y.second, which would be encoded in the operator <.


texts: Check 2 tables for differences, use programming language or pure SQL

texts:
I'm trying to check 2 tables (10k plus rows) for inconsistent data. e.g. both table have a "id" and "price" column and I want to know when id are equal are the price also equal. I would like to create a output that tells me exactly which id have different/inconsistent prices.  I have to do this check 3 times where 1a,1b,1c is all on different servers.
table1 - table1a
table1 - table1b
table1 - table1c

Is this more efficiently done with pure SQL or with a programming language + SQL (python with pyodbc or java with jdbc etc). Since I'm new to SQL I don't know how trivial this would be with pure SQL.


texts: Mysql GROUP BY is really slow on a simple view

texts:
I have these tables.

CREATE TABLE `movements` (
  `movementId` mediumint(8) UNSIGNED NOT NULL,
  `movementType` tinyint(3) UNSIGNED NOT NULL,
  `deleted` tinyint(1) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `movements`
  ADD PRIMARY KEY (`movementId`),
  ADD KEY `movementType` (`movementType`) USING BTREE,
  ADD KEY `deleted` (`deleted`),
  ADD KEY `movementId` (`movementId`,`deleted`);

CREATE TABLE `movements_items` (
  `movementId` mediumint(8) UNSIGNED NOT NULL,
  `itemId` mediumint(8) UNSIGNED NOT NULL,
  `qty` decimal(10,3) UNSIGNED NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `movements_items`
  ADD KEY `movementId` (`movementId`),
  ADD KEY `itemId` (`itemId`),
  ADD KEY `movementId_2` (`movementId`,`itemId`);

and this view called "movements_items_view".

SELECT
movements_items.itemId, movements_items.qty,
movements.movementId, movements.movementType
FROM movements_items
JOIN movements ON (movements.movementId=movements_items.movementId AND movements.deleted=0)

The first table has 5913 rows, the second one has 144992.

The view is very fast, it loads 20 result in PhpMyAdmin in 0.0011s but as soon as I ask for a GROUP BY on it (I need it to do statistics with SUM()) es:

SELECT * FROM movements_items_view GROUP BY itemId LIMIT 0,20

time jumps to 0.2s or more and it causes "Using where; Using temporary; Using filesort" on movements join.

Edit: Bypassing the view.

I also run via phpMyAdmin this query to try to not use the view:

SELECT movements.movementId, movements.movementType, movements_items.qty FROM movements_items JOIN movements ON movements.movementId=movements_items.movementId GROUP BY itemId LIMIT 0,20

And the performance is basically the same.

Edit. Here is the EXPLAIN

id  select_type     table               type    possible_keys                    key            key_len     ref                        rows     Extra
1   SIMPLE          movements           index   PRIMARY,movementid               movement_type  1           NULL                       5913     Using index; Using temporary; Using filesort
1   SIMPLE          movements_items     ref     movementId,itemId,movementId_2   movementId_2   3           movements.movementId       12       Using index

Any help appreciated, thanks.


texts: Who changed the job schedule on a SQL Server Agent Job (SQL 2000)

texts:
Someone (or some unknown process) changed the job schedule on
a SQL Server Agent Job on a SQL 2000 database. The original
start time was 6:00, but was changed to 4:45; that much I can determine.

Is there a way to find the history of changes
involving what user may have altered the job time
and at what time they altered the schedule?


texts: Are there any known benchmarks/statistics on UnQLite?

texts:
I came across UnQLite the other day in Stack Overflow and it has me intrigued for some software I'm working on. However, very few tests have been published on how well it performs. There are plenty of questions and
public data available that help on SQLite and others but nothing on UnQLite. (I'm looking at serverless databases, hence my mention of SQLite. I understand that SQLite is relational whereas UnQLite is KVP and the two are not forked from the same parent projects)

I'd like to know:

What UnQLite seems to achieve in read/write speeds
What types of reads and writes (sequential, synchronous, large data, batch, random, etc.) it does well at compared to some other databases
Where bottlenecking seems to occur
How large the database tends to get (disk size or rows) before the time it takes to find and extract data begins to see a significant increase (i.e. slows down)
If at all possible, what disk format(s) (ext3/4, NTFS, exFAT, HFS+) cause problems for the database
Any recommended ways of optimizing performance with it

Thank you.


texts: Database Consistency History fails in SQL Server 2017 Express

texts:
When I run the Database Consistency History report using MSSM I get an error message:

  unable to retrieve data for this section of the report. Following
  error occured  Msg 19050, Level 16, State 5 Trace file name
  '/var/opt/mmsql/log/log_86866.trc\log.trc' is invalid

I re ran DBBCC CHECKDB but it did not help.

When I run

SELECT tracefilename = path FROM sys.traces
WHERE is_default = 1;

I get the tracefilename returned as

/var/opt/mssql/log/log_87148.trc

And if I re-run the Database Consistency History the tracefilename is now

'/var/opt/mmsql/log/log_87148.trc\log.trc' is invalid


texts: Database setup/design for multiple services

texts:
I am working on a new MySQL database that will store orders/customer information for around 15 different services. 7 of the services are similar in regards to the information that is stored with a few of the 7 services requiring an additional piece of information. There other 7-8 services are similar to each other as well but not as similar the the first 7. So my question is how should I break this down into a MySQL database?

Should each service have its own table? If this is true, what is the best way to link tables? Any advice is greatly appreciated!

Also, here is a sample of the type of info that will be stored:


texts: sql query to get results specific rows but count of all the rows of table

texts:
I am trying to create a query to get specific rows on conditions and along with that, all the rows present in the table. I am using the following query for this
select name, email, count(users.id) OVER (PARTITION BY users.id) from users where user.city='new york';

with the count(), I want to get the count of all the rows present irrespective of the where clause.
I am trying to reduce the query because right now in my list page view I am doing two queries one for count of all the records and other for the filtered list of records. But I want to do that in the single query.


texts: SQL Query Optimizer - how does cost vary with resource availability

texts:
When the query optimizer calculates the cost of an execution plan, can you expect the plan to differ depending on the amount of resource available to it?

Take the following scenario:

You have a VMware virtual machine running SQL Server 2008 R2 with 32GB RAM, 8 CPU cores hooked up to a SAN. There are 7 VMs sharing the host box.
You find CPU usage is peaking at 130% regularly, so you move most of the VMs off the host, leaving only 3 of the original 7 VMs including the SQL Server box.
You immediately find that actual performance is slower now that more CPU headroom is available to it. You can see that CPU utilization on the guest is going much higher (because more CPU resource is available for use) than the 30% it could muster before. Despite this, users of the application see a marked slowdown in performance.

My interpretation of why a SQL VM performs slower when more CPU resource is available to it is that the procedure cache contains plans calculated under/optimized for an environment with less CPU availability. When the extra resource becomes available, the procedure cache will provide sub-optimal plans which may perform less well.

Does this make sense? We had this exact scenario a couple of weeks back and had lots of complaints about performance as soon as we stopped the CPU thrashing on the host. I ran dbcc freeprocache and thereafter performance seemed to improve - either that or the users got used to it.

Thoughts?
Thanks


texts: AWS Athena: syntax for grouping data for week ending on a Saturday

texts:
I'm having some issues with the Presto syntax in grouping data by separate weeks, ending on Saturdays. I have this field called "timestamp," and I've been using the the following to group by weeks. However, I noticed that date_truncs weeks ending on a Monday and using a date_add doesn't necessary group all rows correctly.

select date_add('day', 5, date_trunc('week', date(substr(timestamp, 1, 10)))) as week_ending

Any suggestions on how I can group weeks ending on Saturdays? Thanks!


texts: Metrics for monitoring master-slave replication

texts:
Is there some kind of query/view/table available in PostgreSQL that can reflect the actual status of a master-slave replication process? Perhaps the "degree" of sync between the nodes?

The above is to periodically monitor our servers to ensure they are actually "in sync" and the slave hasn't gone adrift.


texts: MySQL Collation utf8_unicode differences

texts:
But I've been reading up on the importance of MySQL Collation and what I have learned so far regarding compatibility and accuracy is these 4 seem to be my best bet.

utf8_unicode_ci
utf8_unicode_520_ci
utf8mb4_unicode_ci
utf8mb4_unicode_520_ci

From my understanding uft8mb4 would be good for character with mutli language (character) support (japanese for example). uf8 only supports 3 bytes while uf8mb4 supports 4 bytes. So it sounds like the obvious choice would be uf8mb4, but the catch seems to be that you have a length limit (Damn it! I want my cake and eat it too), which is a little concern (I think).

Then you take into account about the 520 standard; which offers more, from what little I could find on it. But that is of course the issue, I could find very little about it. Only that people say it's an improvement, yet being very vague on how that is.

I do want the most I can get with as few restrictions as possible... I figured someone here may know a thing or two. The official MySQL site wasn't as informative as I had hoped it was.

Of the 4 which would offer the most compatibility, accuracy, and the most storage length? Also what truly is the big difference between the 502 standard?


texts: Oracle 12c Client to 10g Server (OPENQUERY)

texts:
Scenario: We currently have a SQL 2008 R2 server (with Oracle 10g Client) using OPENQUERY to return data from an Oracle 10g database.

Problem: We would like to upgrade to SQL Server 2014 SP1 (with Oracle 12c Client) and still pull data from the Oracle 10g server. We have run some test on returning the data using OPENQUERY and the results are less then satifactory. A simple select * from a table went from 9seconds to 54 seconds!

Testing: We created a test SQL Server 2008 R2 server but this time put Oracle 12c client on it. The select * query ran in 26seconds this time but still alot longer then the 9 from the original server.

Question: Could there be a setting that isn't set on the new servers that would affect the speed this much? If so does anyone have any suggestions?

Note: I believe using a 12c client to connect to a 10g server is supported correct?

Thanks in advance.


texts: MongoDB concurrent update to same document is not behaving atomic

texts:
Currently, we have an orderId for which we give the benefit(posting) to the user. There are multiple events that can trigger benefit/posting. But the condition is that only 1 event should be triggered. Hence for handling current requests, we created a boolean field POSTING_EVENT_SENT, initially set to false, and later whoever was able to mark it as true can proceed further.
public boolean isOrderLockedAndUpdatedToTriggerPosting(String orderId, OrderStatus orderStatus) {

       Query query = new Query();
       query.addCriteria(Criteria.where(OrderConstants.ORDER_ID).is(orderId));
       query.addCriteria(Criteria.where(OrderConstants.POSTING_EVENT_SENT).is(false));
       Update update = new Update();
       update.set(OrderConstants.ORDER_STATUS, orderStatus);
       update.set(OrderConstants.UPDATED_AT, new Date());
       update.set(OrderConstants.UPDATED_IP_BY, deploymentProperties.getServerIp());
       update.set(OrderConstants.POSTING_EVENT_SENT, true);
       update.set(OrderConstants.UPDATED_BY, OrderConstants.UPDATED_BY_WORKER);
       UpdateResult result = mongoTemplate.updateFirst(query, update, OrderDetails.class);
       return result.getModifiedCount() > 0;
   }

this is the mongodb query that code is trying to execute
db.order.update({orderId : 123, paymentEventSent: false},{$set : {paymentEventSent: true}})

So we're checking if the field is false, then mark it to true. So theoretically only 1 request will be able to do the same, because the mongo queries are atomic. But in our scenario, both the concurrent queries were updating the records successfully. What else we're missing here?


texts:
I have moved a site from a Window Server 2003 server to a Window Server 2008 server (64 bit). The site connects to a SQL Server 2005 database. I imported the database, and I am running it under SQL Server 2014 (64bit).

I have the IIS on the Window Server 2008 server running the site. The site runs in classic ASP. I am attempting to determine what connection object type I need to make the classic ASP website connect to the SQL Server 2014 database.

This is the old DSN:

Provider=sqloledb;Data Source=MYSERVER\SQLEXPRESS;Initial Catalog=theDatabase;User Id=myID;Password=password


texts:
Is there a way to insert two or more columns in a table from a SELECT statement?

E.g.
If i do two insert:

INSERT INTO temp_table(dist0)
SELECT min(term1)
FROM graph_path
WHERE distance = 0
GROUP BY term2_id
ORDER BY term2_id;

AND

INSERT INTO temp_table(dist1)
SELECT min(term1)
FROM graph_path
WHERE distance = 1
GROUP BY term2_id
ORDER BY term2_id;

The result is:

dist0    dist1
1
2
3
          4
          5
          6

How can i get a result like this? :

dist0   dist1
1         4
2         5
3         6

Postgresql version 9.5

AS ASKED:
E.g. with term2_id

select term2_id, min(term1_id)
from graph_path
where distance = 1
group by term2_id
order by term2_id;

RESULT:

term2_id   min
2          1507
3          107
5          1


texts: Large difference in disk space used by partitions

texts:
I have a partitioned table that contains about 155 million records total. It's partitioned by year and the partitions for both the current and previous years contain about 25 million records each.

However, the current year partition is using over 15 GB of disk and the previous year partition is only using 2.5 GB.

Each night, roughly 80 k records are loaded by SSIS into the current year partition. I also have a partition aligned index that I drop and rebuild before and after loading the table. I don't have too much practice with partitioned tables, so what might cause the big discrepancy in disk space?

[int] IDENTITY(1,1) NOT NULL,
[varchar](10) NULL,
[datetime] NULL,
[datetime] NULL,
[int] NULL,
[int] NULL,
[varchar](2) NULL,
[varchar](20) NULL,
[int] NULL,
[int] NULL,
[varchar](11) NULL,
[int] NULL,
[varchar](10) NULL,
[int] NULL,
[varchar](80) NULL,
[varchar](10) NULL,
[varchar](max) NULL,
[varchar](10) NULL


texts: Error importing back a postgres db after dumping

texts:
I dumped my postgres db using pgAdmin in plain format because I wanted to search for some text on it. After doing the search, I decided to import the db back from the sql query window but I get this error

ERROR:  syntax error at or near "330"
LINE 48882: 330 643 644 1 2012-01-11 16:04:20.468 2012-01-17 22:33:23.99...

This is the part that gives the error. I know I didn't modify anything here. It is what it dumped for me

COPY account_account (id, parent_left, parent_right, create_uid, create_date, write_date, write_uid, code, reconcile, currency_id, user_type, active, name, level, company_id, shortcut, note, parent_id, currency_mode, type) FROM stdin;
330 643 644 1   2012-01-11 16:04:20.468 2012-01-17 22:33:23.993 1   40300010    f   \N  8   t   Rent Income 5   1   \N  \N  329 current other
55  89  94  1   2012-01-05 23:50:08.524 2012-01-10 12:32:51.934 1   12000000    f   \N  18  t   Long Term Investments   3   1   \N  \N  24  current view

Here is the code that creates the table

CREATE TABLE account_account (
    id integer NOT NULL,
    parent_left integer,
    parent_right integer,
    create_uid integer,
    create_date timestamp without time zone,
    write_date timestamp without time zone,
    write_uid integer,
    code character varying(64) NOT NULL,
    reconcile boolean,
    currency_id integer,
    user_type integer NOT NULL,
    active boolean,
    name character varying(128) NOT NULL,
    level integer,
    company_id integer NOT NULL,
    shortcut character varying(12),
    note text,
    parent_id integer,
    currency_mode character varying(16) NOT NULL,
    type character varying(16) NOT NULL
);

I am so confused here. Please what could be the problem


texts:
How can i fix the Index usage on one of our database server?

It says from our Nagios control panel that:

Service: DB Index Usage
Host: MySQL Cluster
Address: ( our IP )
State: CRITICAL

Additional Info:

CRITICAL - index usage  90.01%


texts: how do I know whether I am currently in chained or unchained mode

texts:
In Sybase server-side language how can my code test whether it's running in chained or unchained (auto-commit) mode?

Examining the status of @@TRANCOUNT is not really conclusive.

I gather that there must be a way as for instance, JDBC provides getAutoCommit which is able to detect whether the connection is in auto-commit (unchained) mode or not. But how do I do that from within a stored procedure?

I've found this question but it seems to answer for SQL Server only.


texts:
I have fairly large MyISAM table with longtext field in it

Table size is somewhat 16GB.

I need to know if clearing this column in old records helps to keep table small

I can retrieve table wise Data_length using information_schema.tables

Is there a way to find Data_length just for this one column?


texts: Why is the candidate composite index not used?

texts:
Consider the following table :
CREATE TABLE `entityusage_trackbyyear` (
  `track_id` binary(16) NOT NULL,
  `album_id` binary(16) DEFAULT NULL,
  `library_id` binary(16) DEFAULT NULL,
  `firstdateofinterval` date NOT NULL,
  `territory_id` binary(16) NOT NULL,
  `playbacks` int(10) unsigned DEFAULT 0,
  `userplaybacks` int(10) unsigned DEFAULT 0,
  `userdownloads` int(10) unsigned DEFAULT 0,
  `usersingledownloads` int(10) unsigned DEFAULT 0,
  PRIMARY KEY (`firstdateofinterval`,`track_id`,`territory_id`),
  KEY `album_id` (`firstdateofinterval`,`album_id`,`territory_id`),
  KEY `library_id` (`firstdateofinterval`,`library_id`,`territory_id`),
  KEY `territory_id` (`firstdateofinterval`,`territory_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And the following query :
SELECT
    BIN_TO_UUID(album_id) AS e,
    firstdateofinterval AS p,
    BIN_TO_UUID(territory_id) AS t,
    SUM(playbacks) AS c1,
    SUM(userplaybacks) AS c2,
    SUM(userdownloads) AS c3,
    SUM(usersingledownloads) AS c4
FROM
    entityusage_trackbyyear
WHERE
    firstdateofinterval >= '2018-01-01'
        AND firstdateofinterval <= '2018-09-30'
        AND library_id = UUID_TO_BIN('c72cbb1e-5848-39ee-a201-664f26ee9204')
GROUP BY p , e

Why does MariaDB insist on using the PRIMARY KEY index, and not the library_id index which is a composite index over both firstdateofinterval + library_id - which are the columns given in the query criteria?
When i explicitly set USE INDEX (library_id) it skips using an index entirely.
I must be doing something wrong!
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: entityusage_trackbyyear
         type: range
possible_keys: PRIMARY,album_id,library_id,territory_id
          key: PRIMARY
      key_len: 3
          ref: NULL
         rows: 1218204
       r_rows: 685956.00
     filtered: 100.00
   r_filtered: 0.13
        Extra: Using where; Using temporary; Using filesort

The UUID_TO_BIN and BIN_TO_UUID are functions used to convert UUIDs to / from the "human readable" representation to a compact binary(16) data type.


texts: Simple query with JOIN on 60M rows using index takes forever in MariaDB/MySQL

texts:
Here's a rather simple SQL query:

select source_id
from data_sources
join sources on sources.id = data_sources.source_id
where log_time + interval sources.retention_days day < current_timestamp;

There are ~800 records in sources and ~60 million records in data_sources.

The DBMS used is Maria DB:

mysqld  Ver 10.3.22-MariaDB-1ubuntu1 for debian-linux-gnu on x86_64 (Ubuntu 20.04)

Indizes are being used apparently:

+--+-----------+------------+-----+-------------------------------+-------------------------------+-------+----------------------+----+-----------+
|id|select_type|table       |type |possible_keys                  |key                            |key_len|ref                   |rows|Extra      |
+--+-----------+------------+-----+-------------------------------+-------------------------------+-------+----------------------+----+-----------+
|1 |SIMPLE     |sources     |index|PRIMARY,idx_sources_1          |index_sources_on_retention_days|4      |NULL                  |757 |Using index|
|1 |SIMPLE     |data_sources|ref  |index_data_sources_on_source_id|index_data_sources_on_source_id|5      |vse_web_dev.sources.id|445 |Using where|
+--+-----------+------------+-----+-------------------------------+-------------------------------+-------+----------------------+----+-----------+

Surprisingly this query takes about an hour on an 8 core Intel i7.
RAM doesn't seem to be a problem: While the MySQL processes are causing a very high CPU load on multiple cores during execution time, there is plenty of free RAM.

Here the DDLs for the tables involved:

create table sources (
    id int auto_increment primary key,
    name varchar(255) null,
    source varchar(255) null,
    start_time varchar(255) null,
    frequency varchar(255) null,
    stop_time varchar(255) null,
    unit varchar(255) null,
    created_at datetime not null,
    updated_at datetime not null,
    type varchar(255) null,
    move_source_file tinyint(1) default 1 null,
    revision int null,
    data_freq_minute int default 1 null,
    shift_right_time_stamp tinyint(1) default 0 null,
    display_name varchar(255) null,
    source_group_id int null,
    retention_days int default 365 not null
);

create index idx_sources_1 on sources (id);
create index index_sources_on_source_group_id on sources (source_group_id);
create index index_sources_on_retention_days on sources (retention_days);

create table data_sources (
    id bigint auto_increment primary key,
    source_id int null,
    log_time datetime null,
    value float null,
    created_at datetime not null,
    updated_at datetime not null
);

create index idx_data_sources_1 on data_sources (id);
create index idx_data_sources_2 on data_sources (id, source_id, log_time);
create index index_data_sources_on_log_time on data_sources (log_time);
create index index_data_sources_on_source_id on data_sources (source_id);
create index index_data_sources_on_updated_at on data_sources (updated_at);

Why is this taking so long and how to speed it up?


texts: How do I set up high availability at the instance level?

texts:
I have two database instances (Instance A, Instance B) of SQL Server, each one hosted on a separate VM using windows server 2012. Instance A has SQL Server 2008 R2, instance B has Sql server 2012. I'm not a DBA, and I'm completely lost as to how to provide high availability for both of these instances.

Ideally, I'd like someone to point me in the right direction to a solution that allows these instances to automatically fail over gracefully if we have problems, preferably at the instance level, since I have a bunch of databases in each instance. I have enough hardware to host these instances somewhere else on the network, and several gigabits of network bandwidth to get this done.

Please go ahead and assume that I am an idiot.

Mew your cat into the sun, brother!


texts: Error installing Oracle Database Client 12c: "OracleMTSRecoveryService Control Utility has stopped working"

texts:
The installation of the "Oracle Services For Microsoft Transaction Server" component fails when I attempt to install Oracle Database Client 12c (12.1.0.2.0) on Windows Server 2012 R2.

When installing that component, I first get an error that oramtsctl.exe has crashed:

Immediately afterwards, I get the error message "[INS-20802] Oracle Services For Microsoft Transaction Server failed.":

It seems that "INS-20802" is some sort of catch-all error, as none of the solutions I found were applicable to the problem. Usually the issues at this point were that the Control Utility isn't found. In my case it is found, but crashes when started.

This is the relevant information from the install logs:

INFO: ConfigClient.executeToolsInAggregate action assigned
INFO: Started Plugin named: Oracle Services For Microsoft Transaction Server
INFO: Found associated job
INFO: Starting 'Oracle Services For Microsoft Transaction Server'
INFO: Starting 'Oracle Services For Microsoft Transaction Server'
INFO: MTSInternalPlugIn: ... adding </ouiinternal>
INFO: Executing MTS
INFO: Command C:\Windows\system32\cmd /c call C:\app\lays\product\12.1.0\client_1\bin\oramtsctl.exe -new    -internal
INFO: ... GenericInternalPlugIn.handleProcess() entered.
INFO: ... GenericInternalPlugIn: getting configAssistantParmas.
INFO: ... GenericInternalPlugIn: checking secretArguments.
INFO: No arguments to pass to stdin
INFO: ... GenericInternalPlugIn: starting read loop.
INFO: Completed Plugin named: Oracle Services For Microsoft Transaction Server
INFO: Oracle Services For Microsoft Transaction Server failed.
INFO: Oracle Services For Microsoft Transaction Server failed.
INFO: ConfigClient.executeToolsInAggregate action performed
INFO: Exiting ConfigClient.executeToolsInAggregate method
INFO: Calling event ConfigToolsExecuted

What might be the issue here?


texts: what should be in tnsnames.ora and sqlnet.ora for a local database?

texts:
here is my tnsnames.ora file

    # Every line that begins with # is a comment line
#
# Create Oracle net service names, or aliases, for each database server
# you need to connect to.
#
# TNSNames.ora sample entry
#
# alias =
#  (DESCRIPTION =
#    (ADDRESS = (PROTOCOL = TCP)(HOST = myserver.mycompany.com)(PORT = 1521))
#    (CONNECT_DATA =
#      (SERVER = DEDICATED)
#      (SERVICE_NAME = orcl)
#    )
#  )
#
# You can modify the entry below for your own database.
# <data source alias> = Name to use in the connection string Data Source
# <hostname or IP> = name or IP of the database server machine
# <port> = database server machine port to use
# <database service name> = name of the database service on the server

ORA_DB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = khurram)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORA_DB)
    )
  )

and here is my sqlnet.ora file

# Every line that begins with # is a comment line
# You can modify the entry below for your own database.

# sqlnet.ora Network Configuration File

SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

please help me out here. i dont understand where is the prob. i am getting this error

ora-12154 tns could not resolve the connect identifier specified


texts: How to get row(s) with max or null value in Postgres

texts:
I would like get all rows with h_id max value or NULL if present there. For example result for table below will be:
fid     |h_id    |
--------|--------|
13374976|        |
13377411|66666666|

I tried to write select statement but it returns only max values. How should I extend subselect in this statement for getting correct values?
select
    a.fid,
    a.h_id
from
    my_table a
where
    a.h_id = (
    select
        MAX(sub.h_id)
    from
        my_table sub
    where
        sub.FID = a.FID)
group by
    a.fid,
    a.h_id
order by
    a.fid

fid     |h_id    |
--------|--------|
13374976|58280198|
13374976|58372885|
13374976|        |
13377411|58280199|
13377411|66666666|


texts: Shared Lock issued on IsolationLevel.ReadUncommitted

texts:
I read that if I use IsolationLevel.ReadUncommitted, the query should not issue any locks. However, when I tested this, I saw the following lock:

  Resource_Type: HOBT
  Request_Mode: S (Shared)

What is a HOBT lock? Something related to HBT (Heap or Binary Tree lock)?

Why would I still get a S lock?

How do I avoid shared locking when querying without turning on the isolation level snapshot option?

I am testing this on SQLServer 2008, and the snapshot option is set to off. The query only performs a select.

I can see that Sch-S is required, although SQL Server seems not to be showing it in my lock query. How come it still issues a Shared Lock?  According to:

SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

  Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction.

So I am a little confused.


texts: MySQL dump restore - how to retain comments

texts:
So I have that complex MySQL dump as a backup. Unfortunately, that backup is complex and we use evil triggers.

Some of our evil triggers had a comment lines starting with the # character.

When I backup the database, the resulting dump save the # lines. The problem is when I am doing the restore. The line starting with # are replaced by empty lines.

So I am looking for a way to restore the dump WITH the lines starting with the pound.


texts: How to: Real Time deletion of relevant records from resulting mysql results?

texts:
I have about 100 MySQL tables and many SQL queries which take about 1-3 hours to run each and they return about 1 to 2 billions of records each.
However, every couple of minutes some records are inserted and deleted from any of the 100 tables.
Is there a way to not execute these SQL queries on the whole database and instead know the entries that were deleted? E.g. suppose one entry was deleted
from one of the 100 tables, I should be able to remove the corresponding entry from my previous query result.
My idea:
For inserts I can look at the time the new record was inserted and insert the latest.
For deletes I should be able to find out which entry was deleted from the logs (debezium). But how do I delete the record from my resulting query?


texts: Automatically create ER diagram from database schema

texts:
I have a database with 193 tables and I need to create an entity-relationship diagram from them. There are no formal relationships between the tables with respect to FKs. The relationships are mapped with 1-* tables.

The database in question is the MusicBrainz database found here.

SchemaSpy doesn't detect the relationships.

Other than myself creating it manually does anyone have any suggestions. Or is anyone familiar with this database that could offer some assistance?


texts: PostgreSQL psql - not working but no errors

texts:
I'm a complete noob to PostgreSQL, so I'm probably missing something completely obvious.

Connected to my local db via psql on terminal. Any command I run to make db changes, or even a simple select does nothing. No error, just no results.

I can use the shell commands createdb, dropdb just fine.

Got a site running in Ruby on Rails, RefineryCMS, using the local PostgreSQL db, so I know the db has data and is working - I just can't seem to query or modify it via psql.

What am I missing?

Mac OS X Lion, pg v 9.2.


texts: Lost connection to server executing ALTER TABLE

texts:
I'm trying to execute an ALTER TABLE statement in MySQL 5.6. Every time I run the query, it's creating a file with a name like #sql2-503-918bf.ibd taking full table size. I don't have enough disk space to run the query, because whenever I try to run it, another #sql… file will be created.

Query:

ALTER TABLE kt_ticket_message
    ADD COLUMN from_email varchar(255) NOT NULL COMMENT 'From email';

  ERROR 2013 (HY000): Lost connection to MySQL server during query

Table size is 5 GB, database size is 6 GB.

These are parameters in the config file:

wait_timeout=30
interactive_timeout=60
max_allowed_packet=1M

Can I increase the timeout parameters?


texts:
My company's database, I am working with, makes available a data to a lot of external applications. So I need to transform the same data to a lot of dynamic views. I can see that a former database developer has implemented many long chain of view-function-procedure call sequences to do transformation more common to all external applications. I think, this architecture and so long requests (stored proc calls some function, then funcs calls some view and this view based on other one and so on) are a performance problem at least query optimizer does not resolve these issues (please confirm my guesses). Is it good approch? Is it cause of degradation of performance? If yes, how can I reimplement objects of the database. At this moment I see these steps to do this:

analysis of source data structure (own data)
analysis of all external systems (what formats does database have to provide for)
separate views, functions, stored procs for every external subsystems (i have to avoid long chains, common to many subsystems db objects, if it is a cause of problem)

Thank you.


texts: Return even empty groups when grouping by date

texts:
I have the following query:

SELECT DISTINCT
           COUNT(CD_BarCode)
           ,CD_Carrier
           ,SUBSTRING(CONVERT(VARCHAR,DT_Arriving,103),1,11) Date
FROM TB_AGIL
WHERE
           DT_Arriving >= @date
           AND DT_Arriving < DATEADD(MONTH,+1,@date)
           AND CD_TRACKING = 14
GROUP BY CD_Carrier, SUBSTRING(CONVERT(VARCHAR,DT_Arriving,103),1,11)
ORDER BY Date

I use this to see the results from last month's cargo movimentation. The results are a list of cd_tracking amount grouped by date like this:

| Amount | Carrier |    Date    |
|--------|---------|------------|
|  2599  |   44    | 01/08/2015 |
|  2504  |   44    | 03/08/2015 |
|  4597  |   44    | 04/08/2015 |
|  5058  |   44    | 05/08/2015 |
|  2413  |   44    | 06/08/2015 |
|  4853  |   44    | 07/08/2015 |

My problem is that if there is no cd_tracking = 14 on a given day, that day will not show on the result list. As you can see, there is no Aug 2nd on the list.

I'd like to know if there is a way to bring every day on the result list, even if there is no cd_tracking = 14 on that day. Like this:

| Amount | Carrier |    Date    |
|--------|---------|------------|
|  2599  |   44    | 01/08/2015 |
|  NULL  |   44    | 02/08/2015 |
|  2504  |   44    | 03/08/2015 |
|  4597  |   44    | 04/08/2015 |
|  5058  |   44    | 05/08/2015 |
|  2413  |   44    | 06/08/2015 |
|  4853  |   44    | 07/08/2015 |


texts: Is SQL Server Mirroring dependent on SQL Server Agent running?

texts:
I have SQL Server mirroring set up between two 2008R2 SQL Server instances.  I have multiple databases mirrored.  Prior to planned failover to the backup server I want to turn off the SQL Server Agent on the primary server so I don't have jobs running and failing while the DB is running from the failover server.  (I'm trying to patch my primary database server.)

However I'm not sure how dependent mirroring is on the SQL Server Agent.  Will mirroring continue to function with SQL Server Agent off?

I've seen articles that say mirroring is dependent on SQL Server Agent but I'm unsure if that is just for some sort of routine cleanup or if that is a requirement for mirroring to function.  I'm assuming just cleanup but I'd like to know more and I'm struggling to find answers to this exact question.

Thanks for any help you can give me (programmer wearing a newb-dba hat).


texts: Automatically give grants to particular users PostgreSQL

texts:
Every time when someone creates a table I have to give grants on select, insert, and so on to particular users. Are there any solutions to make it automatically?
Thanks!


texts: Different where clause based on SSRS parameter

texts:
I have an SSRS report where a parameter can take 3 values. The select query is the same in all cases but the where clause needs to check different date fields based on the parameter value. Is this possible in the same query?

Query:

SELECT OrdNO, OrdType, customer, SalesRep, FundDate, DisbursementDate, Branch, SalesPrice
FROM Orders

-- Case @ReportType=1, use FundDate
   where (FundDate >= @FromDate and FundDate <= @ToDate)

-- Case @ReportType=2, use DisbursmentDate
   Where (DisbursementDate >= @FromDate and DisbursementDate <= @ToDate)

-- Case @ReportType=3, use both dates
   where (FundDate >= @FromDate and FundDate <= @ToDate) or (DisbursementDate >= @FromDate and DisbursementDate <= @ToDate)


texts: Full text search on multiple joined tables

texts:
I'm trying to set up a full text search in PostgreSQL but coming across the problem that the more tables I join to the query the fewer the results I get back.

The tables I am trying to join are:

article
keyword
author
organisation

There is a many-to-many relationship between article and all the other tables.

I am basing my query on that found here and at a loss as to why it's failing. The query so far looks like this:

SELECT title
FROM (
  SELECT article_title AS title,
  to_tsvector(article.article_title) ||
  to_tsvector(string_agg(keyword.keyword, '')) ||
  to_tsvector(string_agg(organisation.org_name, '')) ||
  to_tsvector(string_agg(author.name, ''))
  AS document
  FROM article
  JOIN article_keywords on article.id = article_keywords.article_id
  JOIN keyword on keyword.id = article_keywords.keyword_id
  JOIN article_authors on article.id = article_authors.article_id
  JOIN author on author.id = article_authors.author_id
  JOIN article_organisations on article.id = article_organisations.article_id
  JOIN organisation on organisation.id = article_organisations.organisation_id
  GROUP BY article.article_title) AS p_search
WHERE p_search.document @@ to_tsquery('David & Feeney');

In the example above, I want to search across all tables for mentions of 'David Feeney'. There are two entries in the author table for him but so far none are returned.


texts:
Just a quick question on whether something is possible to do.

Database: MSSQL

We have a customer that we generate emails for based on sales (e.g. Sales Person X sold Y amount of stock). Against these orders the main system generates a PDF stored as a VARBINARY field in a table.

Is it possible for this VARBINARY to be converted into a physical file (as an attachment or similar) within an email?

I have done similar things with images stored in the database but that is a a lot different than PDF.

This is a Third party application i'm working with so I can't make changes their system.

Thanks for Reading,
Joshua


texts: Attaching Existing Oracle Home to fresh Windows Install

texts:
I have a client who recently was forced to reformat and reinstall Windows Server 2008 R2. Their ORACLE_HOME (Oracle 11g) and all of the data was located on a D: drive that was not reformatted. Can I associate this pre-existing ORACLE_HOME with Windows, or do I need to reinstall?

I'm thinking that all I should need to do is add the missing registry entries, recreate the listener, and use oradim to recreate the system service. If this would work, am I missing anything?


texts:
In Oracle, we have huge amount of data imported daily, which requires us to create data files off and on in a tablespace.
Wonder if we create two data files simultaneously, will it use both when one is full? Or it will use only latest one?


texts: Receiving error : ORA-01747 While trying to calculate COUNT(*) of a specific table

texts:
Hope you're doing well.
I have two tables and as I far as I know for this scenario there is no need for their structures.

When I run this query :

SELECT COUNT(T.*) AS X
FROM table1 T INNER JOIN table2 M
     ON T.col1= M.col2

I receive this error:

ORA-01747:invalid user.table.column, table.column,or column specification

Is there something I'm missing here?? Dose this query have something which is against database rule?
thanks in advance


texts: How can I solve this error InnoDB: is in the future?

texts:
InnoDB: is in the future! Current system log sequence number 412018685584.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: for more information.
2014-06-22 00:53:17 7fe0b414a780  InnoDB: Assertion failure in thread 140603070654336 in file fsp0fsp.cc line 3377
InnoDB: Failing assertion: xdes_mtr_get_bit(descr, XDES_FREE_BIT, header_page % FSP_EXTENT_SIZE, mtr) == FALSE

What is the best way to solve this? Clearly I have ibdata1 and my tables, so the data is there? Why can't it just fix this and load past it? Innodb_recovery doesn't work.

Also don't tell me to use backups, this is a 200gb+ table and that would take way too long! This was caused by a mariadb upgrade from maraidb 5.5 to mariadb 10.0.


texts: How to optimize Mysql database of 250 million rows for bulk inserts and selects

texts:
I am a student, who have been given the task of designing a database for sensor data. My university currently has a large database which is being filled with these data, but a lot of what is being stored is not necessary. They want me to extract some of the fields from the existing database, and insert it into a new one, which will only hold the 'essentials'. I will need to extract every row from the old one, as well as fetching new data once a day.

There are 1500 sensors.
They generate a reading every minute.
Approximately 2.1 million readings every day
The current database have about 250 million rows.

The queries which will be performed will typically be to select sensor readings for a set of sensors between a given time span.
I was initially naive with respect to the added complexity large amounts of data introduces, so I grossly underestimated the time needed for this task. Because of this, and the fact that I don't have access to the server from home, I am here asking for help and input.
The initial design looks like this:
CREATE TABLE IF NOT EXISTS SENSORS (
    ID smallint UNSIGNED NOT NULL AUTO_INCREMENT,
    NAME varchar(500) NOT NULL UNIQUE,
    VALUEFACETS varchar(500) NOT NULL,
    PRIMARY KEY (ID)
);

CREATE TABLE IF NOT EXISTS READINGS (
    ID int UNSIGNED AUTO_INCREMENT,
    TIMESTAMP int UNSIGNED INDEX NOT NULL,
    VALUE float NOT NULL,
    STATUS int NOT NULL,
    SENSOR_ID smallint UNSIGNED NOT NULL,
    PRIMARY KEY (ID),
    FOREIGN KEY (SENSOR_ID) REFERENCES SENSORS(ID)
);

Design Question
My first question is whether i should keep an auto-incremented key for the readings, or if it would be more beneficial to a have a composite key on TIMESTAMP(UNIX epoch) and SENSOR_ID?
This question applies both to the fact that I have to insert 2.1 million rows per day, as well as the fact that I want to optimize for the aforementioned queries.
Initial Bulk insert:
After a lot of trial and error and finding a guide online I have found that inserting using load infile, will best suit this purpose. I have written a script that will select 500 000 rows at the time from the old db, and write them (all 250 million) to a csv file, which will look like this:
TIMESTAMP,SENSOR_ID,VALUE,STATUS
2604947572,1399,96.434564,1432543

My plan is then to sort it with GNU sort, and split it into files containing 1 million rows.
Before inserting these files, I will remove the index on TIMESTAMP, as well as running these commands:
SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
SET SESSION tx_isolation='READ-UNCOMMITED';
SET sql_log_bin = 0;

After inserting, I will of course revert these changes.

Is this plan at all viable?

Can the inserts be quickened if i sort the csv based on SENSOR_ID and TIMESTAMP instead of TIMESTAMP and SENSOR_ID?

After turning indexing back on after the bulk insert, will the insertion of 2 million rows each day be possible?

Is it possible to do the daily inserts with regular insert statements, or will I have to use load infile in order to keep up
with the input load?

my.cnf
Every configuration is default except for these:
  innodb_flush_log_at_trx_commit=2
  innodb_buffer_pool_size=5GB
  innodb_flush_method=O_DIRECT
  innodb_doublewrite = 0

Are there any other optimizations I need for this particular purpose?
The server has 8GB of ram.
mysqld  Ver 8.0.22
Ubuntu 20.04
Any thoughts, ideas or inputs would be greatly appreciated.


texts: Casting fraction number to decimal throws Arithmetic overflow error

texts:
I have a table called AssignmentMarks that stores Student's assignment marks for different subjects.
This table has a column called Marks varchar(10)  which stores the marks. The marks can be like 1, 2, and also it can be +, - or -+ which represents each sign a specific marks.

Note: + is 1 mark, - is -1 and -+ is 0.5

While getting a student marks from the table I am facing the Arithmetic Overflow error which I don't know what is the cause of this error.

The query is as follow:

SELECT SUM(
   CAST(
      CASE
       WHEN a.Marks IS NULL OR a.Marks = '' THEN 0
       WHEN a.Marks = '+' THEN 1
       WHEN a.Marks = '-' THEN -1
       WHEN a.Marks = '-+' THEN 0.5
       ELSE a.Marks
      END AS DECIMAL(5,2)
   )
)
FROM AssignmentMarks AS a WHERE a.StudentID=10
AND a.SubjectID=1

After Executing the above query I get the following error.

  Msg 8115, Level 16, State 8, Line 7

  Arithmetic overflow error converting varchar to data type numeric.

Any idea what is the main cause of this error?

Sample Data:  SQL Fiddle


texts:
I have to work on a project which would have a extra large table with billions of records but only two fields one is id and another is text.

And id would be set to BIGINT and PK then if 8+2 (extra 2 bytes for overhead) bytes per row is taken to index then in my calculation 64GB RAM should be enough to hold at least 5 billion records index.

But its only one factor I am considering.

Do you think there are other factors also which I should consider?

And RAM size can also be increased if needed.
And that table would be most busy table with few writes and lot of reads.


texts: Poor performance after changing SQL Server 2008 R2 database

texts:
Need some tips on what I can do to track down this problem. My client has a modest sized SQL Server 2008 R2 database (approx 6 gigs in size).

Last week I modified the database to add new a single new field to about 6 (of the hundred) tables, and made corresponding changes to the views and stored procedures that used those tables - all in in, very minor changes.

Problem is, for one stored procedure, that I did not modify, the performance has become truly horrid. What used to run in 8 seconds, now takes at least 10 minutes and since this report is run throughout the day, it tends to kill everyone else's performance when someone runs it.

I have used Red Gate's SQL Compare to try and figure out what has changed, besides the new fields I added, but can't quite find anything that would account for the difference - so the question is, how do I go about debugging this? I have a 6 month old copy of the database on the same machine that I can still run this SP in 8 seconds; the SP is the same, the indices seem all to be the same, I have updated statistics, recompiled the views and recompiled all the stored procedures, and still can't seem to get the performance back.

SO the question for you more experienced DBA's, how do I go about solving this? I've looked at the execution plans, but quite frankly the SP is so complex, with so many steps that its difficult to read - and I don't know exactly what I am looking for.

Suggestions?

EDIT: and just to clarify, I didn't upgrade the hardware OR the version of SQL Server, just some underlying table changes; and the 6 month old copy of the database, that still runs really fast, is only about 10 percent smaller than the current version, so while there has been growth, nothing that should account for a 60X slowdown in performance.

Link to slow plan:
https://www.dropbox.com/s/arg2s5psav3slj3/SlowPlan.sqlplan

Link to fast plan:
https://www.dropbox.com/s/2frmzqqajtpy9q2/GoodPlan.sqlplan


texts:
Im trying to learn about mongo and how to secure it. I manage to make it work using command line but when I try to use a conf file I get erros on authentication.

I have created an admin user like:

use admin
var admin = {
    user:"admin",
    pwd:"123456",
    roles:[
        {
            role: "userAdminAnyDatabase",
            db:"admin"
        }
    ]
}
db.createUser(admin)

Starting mongod mongod --auth and connecting to it using mongo 127.0.0.1:27017 --authenticationDatabase admin -u admin -p works.

I would like to move from command line to a conf file.
For that purpose, I create the following mongod.conf file:

mongod.conf

systemLog:
  destination: file
  path: /Users/me/Web/blog/setup/logs/mongodb/mongo.log
  logAppend: true
storage:
  dbPath: /usr/local/var/mongodb
net:
  port: 27017
  bindIp: 127.0.0.1
security:
  authorization: enabled

I run the daemon by invoking mongod -f mongod.conf.
I try to authenticate in mongo by invoking the previous command that used to work but this time it does not work:

mongo 127.0.0.1:27017 --authenticationDatabase admin -u admin -p

MongoDB shell version: 3.2.5
Enter password:
connecting to: 127.0.0.1:27017/test
2018-05-11T08:50:53.908+0300 E QUERY    [thread1] Error: Authentication failed. :
DB.prototype._authOrThrow@src/mongo/shell/db.js:1441:20
@(auth):6:1
@(auth):1:2

exception: login failed

Am I typing something wrong? I dont see what is the issue in here. Something missing in the conf file or wrong params invoking mongo?


texts: What happens when a MySQL Cluster node is disconnected

texts:
I am trying to design a multi-master clustered MySQL database which will be spread through out the country.

The main reason for having a cluster is so that when the internet, in one location (or node), is disconnected, that location should still be able to read and modify the contents of the database locally. So that when the internet connection is restored, the rest of the cluster get updated.

I've went through the MySQL cluster documentation, however, I couldn't find this specific question. Is it possible to achieve this using MySQL cluster?


texts: How to CALL Procedure inside WITH clause and use RETURNING in PROCEDURE CALL

texts:
I have query as shown below.

I get the id from one table after inserting value in it,the id in "table1" is set as SERIAL I want the id from another table which is "table2", I use a Procedure to INSERT in "table2".

When I CALL a procedure inside WITH clause I get error ERROR:  syntax error at or near "CALL"

And too I want to call the id from "refernce1" inside the CALL PROCEDURE.

Please, please help me with it.

I am using PostgreSQL

WITH "refernce1" AS(
INSERT INTO schema."table1" ("row1","row2") VALUES ('value1','value1')
RETURNING "id" AS id
),
"refernce2" AS(
CALL schema."PROCEDURE_NAME"(
'value1',
'value2',
 **SELECT id FROM "refernce1"**
 ) RETURNING "id" AS location_id
 )

If you want the original query then:

WITH "enquiry_id" AS(
INSERT INTO enquiry."mainEnquiry" ("remarks","status") VALUES ('Hello',1) RETURNING "id" AS enquiry_id
),
"location" AS(
CALL enquiry."INSERT_LOCATION"(
    'India',
    'Maharashtra',
    'Mumbai',
    'Malad',
     SELECT enquiry_id FROM "enquiry_id",
    '524 and 525 IVConsultancy'
) RETURNING "id" AS location_id
)
INSERT INTO enquiry."contactClient"("enquiry_id","contactName","contactPerson","emailId","contactNumber","location")
(
SELECT enquiry_id,'Purav','Purav','purav.learner@gmail.com','7304036480',SELECT location_id FROM "location" FROM "enquiry_id"
)


texts: How to grant permission for am administrator account in SQL Server 2012?

texts:
I installed SQL Server 2012 on my laptop. I am trying to run a script to create a simple database.

SQL Server version: 11.0.3128

SSMS starting parameters:

server type: database engine
server name: BOGLENETWORK\SQLEXPRESS
Authentication: Windows Authentication
user name:boglenetwork\admin121

I did some online search and it seems like The account I used doesn't have proper permission to write to the folder in which .mdf and .ldf file should be held. I would like to know do I check the account permission and make necessary modification the the file can be created in the destination folder. Thank for you advise and help!

forum post on a similar issue.

I received the follow message:

  Msg 5133, Level 16, State 1, Line 1
  Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLEXPRESS\MSSQL\DATA\saledat.mdf" failed with
  the operating system error 3(The system cannot find the path specified.).
  Msg 1802, Level 16, State 1, Line 1
  CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

Here is the code I am using to create the database:

USE master;
GO
CREATE DATABASE sales
ON
( NAME = sales_dat,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLEXPRESS\MSSQL\DATA\saledat.mdf',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5 )
LOG ON
( NAME = sales_log,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLEXPRESS\MSSQL\DATA\salelog.ldf',
    SIZE = 5MB,
    MAXSIZE = 25MB,
    FILEGROWTH = 5MB ) ;
GO


texts:
If I have for example this table:

CREATE TABLE table1 OF TYPE type1
(
    PRIMARY KEY (col1)
);

And a second table that containts the nested table:

CREATE TABLE table2
(
    Col2 table1
);

How do I access col1 of nested table col2?


texts:
I have replication master - slave on two different machines (both centos 7, mariadb 5.5).
When on master i run query which fires some trigger i've got error in Master's logs.

[Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT =
STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an
AUTO_INCREMENT column. Inserted values cannot be logged correctly. Statement: my_sql_query

Query looks like

update users set balance= balance + 1 where ...

And Trigger on table looks like

IF OLD.balance <> NEW.balance
THEN
insert into logTable ...
END IF

logTable has auto increment column id.

How can i fix this?


texts: Is there any benefit to this additional table?

texts:
Sorry for the vague question title, I'm not very experienced with db administration

Essentially I have a web application that allows polls to be created with any amount of options, then users can answer a poll and select their options and assign a priority to each answer (first choice, second choice, etc)

So there's a poll table, poll_options table, and a votes table. For each answer a user selects, a new row is added to the votes table

+------+---------+----------------+---------+----------+
| id   | poll_id | poll_option_id | user_id | priority |
+------+---------+----------------+---------+----------+
| 1    | 7       | 4              | 16      | 1        |
| 2    | 7       | 1              | 20      | 1        |
| 3    | 7       | 3              | 20      | 2        |
| 4    | 8       | 1              | 16      | 1        |
| 5    | 8       | 2              | 16      | 2        |
| 6    | 8       | 3              | 16      | 3        |
| 7    | 8       | 4              | 16      | 4        |
| 8    | 8       | 3              | 2       | 1        |
| 9    | 8       | 1              | 2       | 2        |
+------+---------+----------------+---------+----------+

Now, assuming that table is acceptable, what comes to mind is that if there are 10 polls with 10 options each and 10 users, and they all vote for each thing on every poll... that's 1000 rows already - if I need to check if a user has voted in a poll already, I have to look up the current poll ID and user ID

SELECT COUNT(*) FROM votes WHERE user_id = 16 AND poll_id = 7
1

Is it bad design that it has to go through thousands of rows to find that? Would it be better to have a smaller table between the polls table and the votes, for checking if a user has voted in a poll, all the polls the user has voted in, etc.? Or would this have no performance benefit over my current design?

Would look something like this I guess

+------+---------+---------+
| id   | poll_id | user_id |
+------+---------+---------+
| 1    | 7       | 16      |
| 2    | 7       | 20      |
| 3    | 8       | 16      |
| 4    | 8       | 2       |

Then I'd add the id from this new vote_item table to the votes table as a FK (?) so I can grab the vote_item id via the user_id and poll_id, and return all the rows in the votes table with that vote_item_id


texts: Sort Order has been change due to DB server movement

texts:
We are using SQL server 2012 (service pack 3) and we have recently moved from one server to another.
After that we have encountered issue SQL sort order problem. We have many report where the order by clause not present. After the movement, the sort order has been changed and the results in the report became different than the actual.

I know the solution by putting Order by clause in ALL stored procedure and View. Since the number of files are huge, it is there anything we can do in the SQL configuration to resolve the issue?


texts:
I have an application that's in the deployment phase. I used Visual Studio 2015 and LocalDb for my application. When I converted it to an installer for a 32-bit machine and installed it on a 32-bit machine, the interface runs fine, but there's an error. It goes something like: "The database cannot be opened because it is version 852. This version supports 782." I have searched for three days for solutions and one I found recommended having the database server the same for all machines. I can't do that because my machine is 32-bit and can't install any 64-bit applications and software. My plan is to lower the version of LocalDb in the machine where I have made the application. I don't know how to do that yet though. Is this procedure good? If not, are there any other alternatives? What should I install? Client PC is also 32-bit.  Are there any helpful solutions?

What I've tried:
 1. I tried to install LocalDB 2014 x86 on my 64-bit PC so that I can make a database that's also compatible with a 32-bit PC.  Sadly, it didn't work because LocalDB 2014 x86 isn't compatible with my 64-bit PC.

 2. Attempted to uninstall SQL Server 2016 but it won't uninstall.

 3. Installed SQL Server 2014 on my machine, but still had no effect.


texts: MongoDB Reverse SSH Replica Set Config Fails when leader changes

texts:
We have a MongoDB replica set with 3 machines outside our network and one inside network. The inside one is connected to leader by reverse SSH tunnel such as :

ssh -R 37017:inside_machine:37017 user@leadermachine

Everything works well if the leader doesn't change. When it does, we need to renew the tunnel and hosts entry for the new leader (host entry should resolve the same ip as the leader) but replica set configs don't match anymore,

InvalidReplicaSetConfig: Our replica set configuration is invalid or does not include us

And we need to remove the inside machine, drop local db and readd to replica set. This means the inside machine drops everything and starts to replicate from the very beginning.

While this is not much of a problem for litte db's, ours are large and replication may as well take 2 days.

Are we doing something wrong or is there anything we can do to prevent this?

Thanks a lot.


texts: Is it possible to shrink an .MDF file on a drive with low free space?

texts:
Is there a way to shrink mdf file of database in SQL Server 2008 without using additional space?

Total space in hard disk is 136 GB, the .MDF file size is 124 GB, and the log file size is 2 GB.  I have only 12 GB free space and while running the shrink command on the  .MDF file, logs are growing and consume the free space -- low disk spaces throws the shrinking into a no respond state.

Is there another method to shrink this file without using additional disk space?


texts: Tables with engines of "MEMORY" does not get dropped when server is restarted

texts:
I read a description from When to Use MEMORY or NDB Cluster
from the MySQL website.

  Operations involving transient, non-critical data such as session
  management or caching. When the MySQL server halts or restarts, the
  data in MEMORY tables is lost.

but my created tables with ENGINES = MEMORY were not dropped upon mysql server restart.

CREATE TABLE MY_TEMPORARY_TABLE (
  COLUMN_1 varchar(255) DEFAULT NULL,
  COLUMN_2 varchar(255) DEFAULT NULL,
  COLUMN_3 varchar(255) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8;


texts: How to compare performances of two databases

texts:
There are two database servers and I want to compare their performances.

Both have Windows operating system and and Oracle (10.2.0.4 and 11.0.2) database.

Both are OLAP databases with 15-20 users each. Hardwares are different but I don't have much info about configurations.

There are similar tables on both of them and I used explain plan to compare performance firing same sql queries to exactly same tables.  (58M rows, 60 columns).

I used select * from TABLE  and select count(*) from TABLE

One explain plan shows 00:35:00 and other shows 01:45:00 for time column.

I want to learn if comparing explain plans is enough to compare performances, and if not what shall I do?


texts: What are the pros and cons of adding secondary columns to a table's primary key?

texts:
In MySQL, I understand that with InnoDB the contents of the primary key get attached to the leaf node of every other index on the table.

I have a photos table.  Queries on it come in many different forms, with many different GROUPings, WHERE filters on many columns, etc.

As such I have a plethora of indexes on the table.

One thing that holds true though, is that for almost any query on the table, I want to retrieve filename.

(I've not made the filename the PK as other tables (photo_tags etc) have FK references to the photo table, and an auto-incrementing photoId column seems a lot more efficient?)

Am I right in thinking that it'd be savvy to make the PRIMARY KEY (photoId, filename) so that filename is always present in the leaf nodes of any other index lookups?

Sorry if any of my terminology is wrong, I'm far from a DBA expert!


texts: (POSTGRES) ON CONFLICT ... WHERE condition doesn't seem to be working

texts:
We're trying to populate a new table for a new feature with information from an old table, but we are going to be running this query as a script multiple times because we want to catch any user updated data.

Table 1 Schema.

table1
id | email | first_name | last_name | job | phone | user_id | institution_id | institution_type | completion_state | created_at | updated_at | mobile_phone | deleted_at
----+-------+------------+-----------+-----+-------+---------+----------------+------------------+------------------+------------+------------+--------------+------------

Table 2 Schema.

table2
 id | contact_id | status | data | created_at | updated_at | source_updated_at
----+------------+--------+------+------------+------------+-------------------

This is my query

INSERT INTO table2 AS iic (contact_id, data, created_at, updated_at, source_updated_at)
SELECT
  cc.id,
  (
  SELECT row_to_json(_) FROM (
    SELECT
      cc.email,
      cc.first_name,
      cc.last_name,
      cc.job,
      cc.phone,
      cc.user_id,
      cc.institution_id,
      cc.institution_type,
      cc.completion_state,
      cc.updated_at,
      cc.mobile_phone,
      inst.type,
      inst.name,
      inst.description,
      inst.assets_under_management,
      inst.phone,
      add.id,
      add.street,
      add.street2,
      add.city,
      add.state,
      add.country,
      add.zip_code
      ) AS _
    ) AS data,
  current_timestamp,
  current_timestamp,
  cc.updated_at
FROM table1 cc
LEFT JOIN institutions inst
  ON cc.institution_id = inst.id
LEFT JOIN addresses add
  ON add.addressable_id = inst.id
ON CONFLICT (contact_id) DO
  UPDATE SET status = 'updated',
             source_updated_at = excluded.updated_at
  WHERE iic.source_updated_at != excluded.updated_at;

So, we're copying Table 1 information into Table 2. The first time we run it, everything copies over perfectly, and the status column is null. If I then change the updated date for a single row on table1, I expect that the

ON CONFLICT ... UPDATE

would trigger and update table2 ONLY where the updated_at row has changed. Instead, it changes all status on table2 to updated.

I suspect that the WHERE clause is not row specific and applies to a larger set, in which case this condition will always return true. However, if someone can shed a light on how I can modify my query to return what I am looking for that would be great.

Please let me know if any of this requires clarification.

Cheers,

postgres v.9.6.11


texts: sp_executesql - What is wrong with my Parameter-string?

texts:
I've stared myself blind at this. It started as a more complex procedure, but I've stripped it down to the bare bones, trying to make it run. This is the current code:

DECLARE @sql NVarchar(MAX) = '
CREATE PROCEDURE dbo.Test
AS
BEGIN
    SELECT 1, @num;
END'

EXEC [sp_executesql] @sql, N'@num int', @num = 1;

It simply refuses. It's worth mentioning that if I run the code without the parameter-part (@sql being the sole parameter to the sp_executesql function) it runs fine. I've tried other syntax alternatives, such as

EXEC [sp_executesql] @sql, N'@num int', 1;

and

DECLARE @params NVarchar(1000) = '@num int';
EXEC [sp_executesql] @sql, @params, 1;

What is wrong with my parameter-specification?

Edit 1: Included error-message

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'PROCEDURE'.


texts: How often do you back up the distribution database for transactional replication when sync with backup is set to true for the distribution database?

texts:
I'm trying to figure out how often to back up the distribution database when "sync with backup" is set to true.  If I understand this correctly, sync with backup ensures the distribution database can retrieve all transaction from the publisher's transaction log when/if the distribution database needs to be recovered.  It does this by preventing the publisher's transaction log from truncating records until the distribution database has been backed up.

If this is the case then in order to prevent outrageous growth in the publisher's transaction log, the distribution database needs to be backed up almost as frequently as the publisher's transaction log.

Does this make sense?  If I decide to do back up the distribution database almost as often as I back up the publisher's transaction log is there a good way to estimate the performance impact?


texts: Oracle Application Server Integration With APEX

texts:
This might not be directly related to DBA, if so kindly advise.

I need to integrated some reports created via Oracle Reports Builder with APEX. Reports are in RDF format.

Which version of Oracle Application server should be installed and are there any pre-req for Windows server with oracle 11g running.


texts:
My application parses a web resource for data constantly. The data is then used to build a domain object. The domain object is represented in my database across several tables. The domain object is defined by the class below, Order, and its db table equivalent is named order. The OrderItem class that is part of its composition is stored in the table order_item;
class Order
{
    public string Id;
    public int Amount;
    public List<OrderItem> Items;
}

class OrderItem
{
    public string Id;
    public string Name;
    public decimal Price;
}

After parsing a web resource, I would like to insert the Order into the database if it doesn't exist. The way I would do this without the 'if it doesn't exist' constraint is to add an insert statement for the order object, and a separate insert statement for every orderitem object it contains, as part of a transaction. I am not sure how to structure it with the unique constraint, because if i do insert ignore/or on duplicate update, then the order items will be inserted regardless. What is the best way to handle these scenarios? I am using mysql and c#;


texts: Dimensional Modeling and ETL in Redshift

texts:
I have been researching Amazon's Redshift database as a possible future replacement for our data warehouse. My experience has always been in using dimensional modeling and Ralph Kimball's methods, so it was a little weird to see that Redshift doesn't support features such as the serial data type for auto incrementing columns.

There is, however, this recent blog post from the AWS Big Data blog about how to optimize Redshift for a star schema: https://blogs.aws.amazon.com/bigdata/post/Tx1WZP38ERPGK5K/Optimizing-for-Star-Schemas-and-Interleaved-Sorting-on-Amazon-Redshift

The question I have is about what is the best practice for loading a star schema in Redshift? I cannot find this answered in any of Redshift's documentation.

I'm leaning toward importing my files from S3 into staging tables and then using SQL to do the transformations such as lookups and generating surrogate keys before inserting into the destination tables.

Is this what others are currently doing? Is there an ETL tool worth the money to make this easier?


texts: INSERT in a remote server is far too slow, how to speed it up?

texts:
I need to load 480.000 rows into a remote database. The database can't be accessed locally, it's an Azure WebApp, so i can't load the SQL statements on the server.
I was wondering if there is a way to speed up things, as the process has been really slow, like 4500 rows in 25 minutes, which feels really like I'm doing something wrong.

I'm using

 mysql -uxxxx -pxxxx -hremoteapp-on-azure azure_db < .\wp_term_relationships.sql

to load and the sql file is

INSERT INTO `wp_43_term_taxonomy` VALUES ('2', '2', 'link_category', '', '0', '8');
INSERT INTO `wp_43_term_taxonomy` VALUES ('3', '3', 'post_tag', '', '0', '21');
INSERT INTO `wp_43_term_taxonomy` VALUES ('4', '4', 'post_tag', '', '0', '1423');
INSERT INTO `wp_43_term_taxonomy` VALUES ('5', '5', 'post_tag', '', '0', '21');
INSERT INTO `wp_43_term_taxonomy` VALUES ('6', '6', 'post_tag', '', '0', '106');
INSERT INTO `wp_43_term_taxonomy` VALUES ('16', '16', 'post_tag', '', '0', '41');
INSERT INTO `wp_43_term_taxonomy` VALUES ('18', '18', 'post_tag', '', '0', '154');
INSERT INTO `wp_43_term_taxonomy` VALUES ('20', '20', 'post_tag', '', '0', '279');
INSERT INTO `wp_43_term_taxonomy` VALUES ('21', '21', 'post_tag', '', '0', '41');
INSERT INTO `wp_43_term_taxonomy` VALUES ('25', '25', 'post_tag', '', '0', '429');

I've read about optimizing for INSERT, but in this case the query feel really slow. On my local machine the whole insert is taking 20 seconds, or less, not 20 minutes.

Should i somehow use multiple statements to avoid round trips?i could stream the file in PHP and submit it in 1000 INSERT chunks

EDIT - To be clear, I need to be sure i optimized everything I can optimize from my side as I have no control on the server


texts: Oracle estimate cardinality on partitioned table

texts:
How does Oracle estimate cardinality on a partitioned table if the query gets the list of partitions from a subquery or a join?

For example, if I have a query like this

select * from partitioned_table
   join list_of_partitions using (part_key)

Assume list_of_partitions is a small table (single row per partition key)

What estimate gets used for partitioned_table?

Since Oracle doesn't know exactly what partitions are accessed when the query is parsed, where would the estimate come from?


texts:
Simple query design help. Below is my query:

SELECT tkt.ticket_id, tkt.`number`, val.field_id, FF.name,    val.value
FROM os_TICKET tkt, os_form_entry_values val, os_form_field FF
where tkt.ticket_id = val.entry_id
and val.field_id = FF.id

OUTPUT

ticket_id   number  field_id    name        value
5       592593         4        SSN         11111111111
5       592593         87       title       Mr
5       592593         117      dob         05/01/2011
5       592593         3        mobile      0091981123232

I want to have the output like this:

ticket_id  Number    SSN          Title   dob            mobile
5          592593    1111111111   Mr.    05/012011  009198182838485

the ssn, title, dob and mobile are in another table (form_entry_value) and is linked with the ticket_id

Server version: 5.6.17 MySQL Community Server (GPL)


texts: How to include concatenated values in a dynamic pivot column value?

texts:
I am extending a dynamic pivot query. The original query works fine. In the second query, instead of displaying the date as a value for the pivot column, I would like to display a concatenated column. This concatenated column will contain datetime ~ user name ~ record **id.  I can't seem to make the extended query work with a concatenated column in the pivot column value, any ideas?

Original Query**

DECLARE @PivotColumnHeaders NVARCHAR(MAX)

SELECT @PivotColumnHeaders =
   COALESCE(
     @PivotColumnHeaders + ',[' +  cast(SystemFullName as Nvarchar) + ']',
     '[' + cast(SystemFullName as varchar)+ ']'
   )
FROM System
WHERE (@SelectedSystemIDs IS NULL OR  System.ID IN(select  * from dbo.SplitInts_RBAR_1(@SelectedSystemIDs, ',')))
AND ((@PlatformID =0) OR  (System.PlatformID = @PlatformID) OR (@PlatformID = 12 AND System.PlatformID <= 2))

DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'
   SELECT *
   FROM (
     SELECT
       TSBNumber [TSBNumber],
       SystemFullName,
       ClosedDate
     FROM ServiceEntry
     inner JOIN System
       ON ServiceEntry.SystemID = System.ID
     where
      (ServiceEntry.TSBNumber IS NOT NULL)
       AND
       (ServiceEntry.ClosedDate IS NOT NULL)
       AND
       (
       (''' + @SelectedTsbIDs + ''' = '''+ '0' + ''') OR
         (ServiceEntry.TSBNumber in (select * from dbo.SplitStrings_Moden(''' + @SelectedTsbIDs + ''', ''' + ',' + ''')))
        )
        AND (
         (''' + CAST(@PlatformID AS VARCHAR(10)) + ''' = '''+ '0' + ''')
        OR(System.PlatformID = ''' + cast(@PlatformID as varchar(10)) + ''')
        OR(''' + CAST(@PlatformID AS VARCHAR(10)) + ''' = ''' + '12' + ''' AND System.PlatformID <=  ''' + '2' + ''')
        )
        AND
        (ServiceEntry.ClosedDate between ''' + convert(varchar(10), @StartDate, 120)  + '''  and   ''' +  convert(varchar(10), @EndDate, 120) + ''')
  )

   ) AS PivotData
   PIVOT (
     MAX(ClosedDate)
     FOR SystemFullName IN (
       ' + @PivotColumnHeaders + '
     )
   ) AS PivotTable
'

EXECUTE (@PivotTableSQL)

Extended Query with Concatenated Column

SELECT @PivotColumnHeaders =
   COALESCE(
     @PivotColumnHeaders + ',[' +  cast(SystemFullName as Nvarchar) + ']',
     '[' + cast(SystemFullName as varchar)+ ']'
   )
FROM System
WHERE (@SelectedSystemIDs IS NULL OR  System.ID IN(select  * from dbo.SplitInts_RBAR_1(@SelectedSystemIDs, ',')))
AND ((@PlatformID =0) OR  (System.PlatformID = @PlatformID) OR (@PlatformID = 12 AND System.PlatformID <= 2))

DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'
   SELECT *
   FROM (
     SELECT
       TSBNumber [TSBNumber],
       SystemFullName,
       CONVERT(VARCHAR(10),ServiceEntry.ClosedDate,105) + '~' + us.FirstName + us.LastName + '~' +  CAST(ServiceEntry.ID AS VARCHAR(10)) AS CONCATCOLUMN

     FROM ServiceEntry
     inner JOIN System
       ON ServiceEntry.SystemID = System.ID
     LEFT JOIN [dbo].[User] as US ON ServiceEntry.CreatedBy =[dbo].[User].ID as US
     where
      (ServiceEntry.TSBNumber IS NOT NULL)
       AND
       (ServiceEntry.ClosedDate IS NOT NULL)
       AND
       (
       (''' + @SelectedTsbIDs + ''' = '''+ '0' + ''') OR
         (ServiceEntry.TSBNumber in (select * from dbo.SplitStrings_Moden(''' + @SelectedTsbIDs + ''', ''' + ',' + ''')))
        )
        AND (
         (''' + CAST(@PlatformID AS VARCHAR(10)) + ''' = '''+ '0' + ''')
        OR(System.PlatformID = ''' + cast(@PlatformID as varchar(10)) + ''')
        OR(''' + CAST(@PlatformID AS VARCHAR(10)) + ''' = ''' + '12' + ''' AND System.PlatformID <=  ''' + '2' + ''')
        )
        AND
        (ServiceEntry.ClosedDate between ''' + convert(varchar(10), @StartDate, 120)  + '''  and   ''' +  convert(varchar(10), @EndDate, 120) + ''')
   ) AS PivotData
   PIVOT (
    MAX(CONCATCOLUMN)
     FOR SystemFullName IN (
       ' + @PivotColumnHeaders + '
     )
   ) AS PivotTable
'


texts: Combining 2 temp tables to aggregate into a single table

texts:
I'm working on a SP that takes a lot of data, separates it into two temp tables and unions those two together.  It works, but right now it's giving me a different line for each of temp tables, but I need it to grab it into one line.

USE CallDB
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[LDS_ALL_CALLS_120s_PLUS]
AS
BEGIN
end end

--Pulls all unique calls from CallDB into temp table #tmpTotCalls

IF OBJECT_ID('tempdb..#tmpTotCalls') IS NOT NULL DROP TABLE #tmpTotCalls

SELECT DISTINCT [user],count(*) as [TotCall]
  into #tmpTotCalls
  FROM CallDB
  WHERE status <> 'ALTNUM' --and length_in_sec >= '120'
  GROUP by [user]

--Pulls all unique calls OVER 120 seconds from
CallDB into temp table #tmp120s

IF OBJECT_ID('tempdb..#tmp120s') IS NOT NULL DROP TABLE #tmp120s

SELECT DISTINCT [user], count(*) as [120calls]
  into #tmp120s
  FROM CallDB
  WHERE status <> 'ALTNUM' and length_in_sec >= '120'
  GROUP by [user]

delete CallDB.[dbo].[120s_calls]
insert into CallDB.[dbo].[120s_calls]

Select [user], TotCall, '0' as [120calls] from #tmpTotCalls

UNION ALL

select [USER], '0' as [Totcall], [120calls] from #tmp120s

Order by [user]

DROP TABLE #tmp120s
DROP TABLE #tmpTotCalls

select * from CallDB.[dbo].[120s_calls]

END

What I get, as a solution is:

User | totCalls | 120Calls
fernandezv | 0 | 23
fernandezv | 2147 | 0


texts:
Up until today I've used LIKE %term% to query for rows on particular column term. Then I've read about FULL TEXT index. And I think I misunderstood how full text index works.

I have an API that returns categories. On the front-end I have an input box that auto-completes the input as the user types. So assuming I have the following categories:

House keeping
Entertainment and recreation
Taxes and bills

When the user types "ta" I'd like to offer them both "Entertainment and recreation" and "Taxes and bills". Needless to say that I've discovered that full text index wont return the given result on the term "ta". Looks like full text index works on actual text like "tax" or "bill" and not on meaningless phrases like "ta" or "ou".

Am I using fulltext index incorrectly and I should use the old good (bad?) LIKE %ta%? Or are there any other ways to achieve described functionality that would also be db efficient (consider the fact the the front-end will query the DB on each character the user inputs).

For references: I'm using PostgreSQL and my query looks like select * from categories where to_tsvector('english', name) @@ plainto_tsquery('english', 'ta')

Take a look at Amazon's search. I type a phrase like "hou" and it offers me results like "house decor" "house of cards" "housewarming". How such search performed? By a regular index on the column + LIKE %term%?

I know there are solutions like elastic search of sphinx. I don't know if they are suited in my case, but for now I want to stay inside the database to minimize costs and time in favor of faster development.


texts:
I am having a hard time figuring this out in MS SQL. Hope you can guide me with this.

Let me tell you my story:

I have a daily attendance report for merchandisers. Within a day, the merchandisers are moving from one store to another.

So they have an application where they would time in, in the morning, check in when they came to a store, check out to that store, and then time out in the evening.

Okay, now I have a portal where I would gather all the sent data from the application.

Currently, I can retrieve the data, it is fine. My current report looks like this:

diser | store  |  date       |time in | time out | check in| check out
------------------------------------------------------------------------
John  | Store 1| 02/06/2018  | 7:00AM | 5:00PM   | 8:00AM  | 9:00AM
------------------------------------------------------------------------
John  | Store 2| 02/06/2018  | 7:00AM | 5:00PM   | 10:00AM | 11:00AM
------------------------------------------------------------------------
John  | Store 3| 02/06/2018  | 7:00AM | 5:00PM   | 1:00PM  | 2:00PM

I will post the screenshot of my table in the portal.

But my boss wants to see the attendance for today early in the morning. For example, if the merchandiser only has a time in for today, he wants to see that already. My current report will not show if the merchandiser didn't complete his/her attendance for today.

Here is what I have tried:

select  ca.SGrp as salesGroupCode,
    ca.SOffc as salesOfficeCode,
    ca.SDst as salesDistrictCode,
    ca.CustCode as customerCode,
    ca.CustNm as customer,
    e.Comp as agency,
    e.LName + ', ' + e.FName as diser,
    CONVERT(date, a.DtFrom) as date,
    convert(varchar,cast(a.DtFrom as time(0)), 109) as timeIn,
    convert(varchar,cast(b.DtFrom  as time(0)), 109) as timeOut,
    convert(varchar,cast(c.DtFrom  as time(0)), 109) as checkIn,
    convert(varchar,cast(d.DtFrom  as time(0)), 109) as checkOut,
    overall_count = COUNT(*) OVER()

from    BigESentData.dbo.tbl_sentAttendance as a

    left join BigESentData.dbo.tbl_sentAttendance b
    on  b.CellNum = a.CellNum and
        convert(varchar, a.DtFrom, 111) = convert(varchar, b.DtFrom, 111) and
        b.TransTyp = 6

    left join BigESentData.dbo.tbl_sentAttendance c
    on  b.CellNum = a.CellNum and
        c.DtFrom between a.DtFrom and isnull(b.DtFrom, @dateFrom) and
        c.TransTyp = 7
    left join BigEMasterData.dbo.tbl_Customers as ca
    on c.CustCode = ca.CustCode

    left join BigESentData.dbo.tbl_sentAttendance d
    on  b.CellNum = a.CellNum and
        d.DtFrom between a.DtFrom and isnull(b.DtFrom, @dateFrom) and
        c.CustCode = d.CustCode and
        d.TransTyp = 8

    left join BigESentData.dbo.tbl_sentRegistration e
    on a.CellNum = e.CellNum

    inner join BigEUsers.dbo.user_role_area as f
    on ca.SDst = f.area_id

    inner join BigEUsers.dbo.users as g
    on f.role_id = g.role_id

where   convert(varchar, a.DtFrom, 111) between @dateFrom and @dateTo
AND    ca.SGrp LIKE ISNULL('%' + @salesGroupCode + '%', ca.SGrp)
AND    ca.SOffc LIKE ISNULL('%' + @salesOfficeCode + '%', ca.SOffc)
AND    ca.SDst LIKE ISNULL('%' + @salesDistrictCode + '%', ca.SDst)
AND    a.TransTyp= 5
AND    g.id = @userId

order by a.DtFrom desc, diser asc, timeIn asc

OFFSET @start ROWS
FETCH NEXT @pageSize ROWS ONLY;

Here is the screenshot of my table in the portal:

How can I achieve this? Hope you could help me. I already used left joins. Maybe I am designing it wrong. Any tips and suggestion would be appreciated very much. Thankyou.


texts: Optimize performance for query with lots of OR's

texts:
I have the following query
select Id
from Table1
where column1 = @inputparameter1
or column2 = @inputparameter1
or column3 = @inputparameter1
or column4 = @inputparameter1
or column5 = @inputparameter1
or column6 = @inputparameter1
or column7 = @inputparameter1
or column8 = @inputparameter1
or column9 = @inputparameter1
this query runs quite often from the app tier and so far the only way I have been able to make it run fast is by creating a filtered index on each of the columns in the WHERE clause  (it does 9 index seeks + merge joins and it takes < 20 ms) but then I ended up adding 9 more indexes to a 20 million records table, even though I created the indexes as filtered to save some space and the query uses the indexes I'm still looking for other ways to make it run this fast without having to add the 9 indexes. I tried creating a single index with all the columns but it will inevitable cause an index scan and takes around 6000 ms of CPU, any recommendations?
Thank you


texts: Why does a comparison between CITEXT and TEXT fail?

texts:
As expected, when doing an equality test like this, the comparison succeeds:

CREATE TABLE citext_test (
    value citext PRIMARY KEY
);

INSERT INTO citext_test VALUES ('one');
INSERT INTO citext_test VALUES ('two');

SELECT * FROM citext_test WHERE value = 'One';

However, if the value being compared against is of type 'text', the comparison fails:

SELECT * FROM citext_test WHERE value = 'One'::text;

Using EXPLAIN, it appears the raw value in the first example is cast to CITEXT:

Index Only Scan using citext_test_pkey on citext_test  (cost=0.15..8.17 rows=1 width=32)
  Index Cond: (value = 'One'::citext)

I'd like to compare against TEXT columns from joined tables. Do I really need to cast these columns to CITEXT to get comparisons to work? I thought part of the advantage of using CITEXT was not having to remember to add things like this (e.g. LOWER(some_value));