texts: Continuously move data from server A to Server B while deleting the data in server A?

texts:
I'm developing an ad server that is expected to handle ad impressions/billion clicks per day.

The most difficult challenge I am facing is moving data from one server to another.

Basically the flow is like this :

Multiple front facing load balancers distributes traffic (http load balancing) to several servers called the traffic handler node.
These traffic handler nodes job is to store the click logs in mysql table (data like geo , device, offer id, userid etc) and then redirect traffic to the offer landing page.
Every minute cron job runs in all traffic nodes that which transfers all the clicks logs to reporting server (server where all reports generation is done) in batches of 10000 rows per minute , and then delete the data after confirming that the data is successfully received by the reporting server. The reporting servers uses clickhouse database engine

I need to replace the mysql database engine from the traffic nodes as I'm facing a lot of issues with MySQL. Between the heavy inserts and then the heavy deletes it's getting slow. Plus, the data is being transferred via cron job so there is 2 minutes average delay.

I can't use clickhouse in these server as Yandex clickhouse do not support updates and deletes yet and the click logs are supposed to be updated many times (how many events happened on the visit)

I'm looking at kakfa but again I'm not sure how to achieve one way data transfer and then deletion of data.

Maybe my whole approach is wrong. I would be very grateful for any expert to guide in right direction.


texts:
I'm trying to add referential integrity to a column. However, I can't know the proper syntax for an alter statement. I've attempted the below code. Can someone tell me what is wrong with this query?

alter table person_address modify column address_addressID int(11) not null,
on update cascade;


texts:
I have a Postgres db server setup on my raspberry pi.  With this I am working on two distinct projects:

Project1
Project2

For these projects I have different users and the users of each should not be able to see the data of the other project.  What i did to enable this was create two databases on the server:

project1_db
project2_db

So my setup on pgadmin4 looks like:

=> Servers

=====> Projects

==========>project1_db

==========>project2_db

I also created two users:

project1_user
project2_user

I made each of these users the owners of their respective db.  By doing this i expected when logged in as project1_user to not be able to see project2_db info (basically be told when clicking the db in pgadmin4 that i dont have permissions).  However, I was still able to click in and then dig into schema/public/tables.  I noticed when looking at privileges for the db in pgAdmin4 that PUBLIC kept being added even after I deleted it - and my understanding is that this makes it viewable by all.

So my questions are:

Is this server/db setup appropriate for what im trying to achieve? If it is, why am i not getting expected privileges?
Is my user setup appropriate / what is the correct user roles setup to use to achieve my goal?  And how does the PUBLIC role tie into this?
Am I missing something obvious / or do i have misunderstanding of the tools?

UPDATE: I just found this PostgreSQL - Who or what is the "PUBLIC" role? on the public role. Which i guess answers the question on public.

I also realized that after I was making updates I was only refreshing my databases not the whole server so the changes werent being pushed down. After I removed public access and refreshed the server the permissions worked as expected.  I am now getting my expected behavior.


texts:
I have trouble scheduling a backup in mysql periodically, most of the tools do not recognize the geometric field, for example navicat which says

  Can not get geometry object from data you send to the GEOMETRY field

others simply do not create the table or do not fill the field with the information, someone knows a solution that is free and effective for this problem

Sorry, forgot to mention, the database is in windows and mysql version is 5.5.25


texts: How to not allow to insert a new record if count of current records exceeds a specified limit

texts:
I have two tables as the following
team
`id` BIGINT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
`member_limit` INT NOT NULL

team_member
`id` BIGINT PRIMARY KEY AUTO_INCREMENT,
`team_id` BIGINT NOT NULL,
`member_id` BIGINT NOT NULL,

CONSTRAINT `fk_team_member_team_id` FOREIGN KEY (`team_id`) REFERENCES `team` (`id`),
CONSTRAINT `fk_team_member_member_id` FOREIGN KEY (`member_id`) REFERENCES `user` (`id`)

My question is on MySQL
How to not allow to insert a new member into team_member when the inserted member's team_id reference to team that count of its current members on team_member exceeds its member_limit


texts: ON DELETE CASCADE on pg_class in PostgreSQL does not work

texts:
I added a foreign key on my table which references pg_class.

ALTER TABLE summary_table
ADD FOREIGN KEY (table_oid_column) REFERENCES pg_class (oid) ON DELETE CASCADE;

I have a summary table which I keep some information about tables with their relational ids. When I drop a table I want to delete automatically related row from my summary table, so I added constraint above. But in my case, when I drop a table nothing happens.

I tried it with a test table, when I delete related row from test table, it works as expected. Such as with this foreign key constraint:

ALTER TABLE summary_table
ADD FOREIGN KEY (table_oid_column) REFERENCES test (id) ON DELETE CASCADE;

What can be the problem? Can it be related with being a catalog table(pg_class) or a hidden system column(oid)?


texts: Cannot find the user, because it does not exist or you do not have permission

texts:
I am using mssql and I would like to achieve this:
Have 1 database, 2 non-SA users. 1st user should be owner of database, 2nd one should have read permissions on some tables, read+write on others.

Tables for 2nd user are created by our application which uses 1st user.

Steps to what I do:
1) I execute script which creates DB + users

CREATE LOGIN USER1
WITH PASSWORD = 'password';
GO
CREATE USER USER1 FOR LOGIN USER1;
GO

CREATE DATABASE my_database;
ALTER AUTHORIZATION ON DATABASE ::my_database TO USER1;

CREATE LOGIN USER2
WITH PASSWORD = 'password';
GO
CREATE USER USER2 FOR LOGIN USER2;
GO

2) My application executes scripts which creates tables + grant privileges on tables to USER2, this fails, because:

Cannot find the user 'USER2', because it does not exist or you do not
have permission.

My question is how to grant privileges to USER1 to add read/write privileges for another users within my_database? I though, that database owner is enough.

Example how I grant privileges to USER2:

GRANT SELECT ON OBJECT ::schema_version TO USER2;


texts: How do you create a relationship to a non-primary key in SQL Server?

texts:
I have a Users table which has two columns a primary key called UserID and another column called UserName.

UserID (int) PK
UserName (varchar(256)

They are both unique but I decided for reasons to use the UserName as a reference in other tables. So the order table for example has a reference to user by UserName not userid.

OrderID
UserName

I want to create a relationship between all tables that reference the UserName and the Users table so that I get the cascade update/delete feature of SQL Server.

But SQL Server doesn't allow me to create a relationship on a non primary key column. Is there any way I can get the cascade update/delete feature without changing the users table so that UserName is the primary key and not UserID?


texts:
SELECT task_name,task_id,
       SUBSTR(task_text,1,20) AS task_text,
       task_project_id,task_status
    FROM projectboard_tasks
    WHERE task_status IN  ('0','3')
     AND  task_assigner = 31 OR  task_assigned_to = 31

Here in query i return the rows with task status 0 and 3 but due to OR the query returns rows which are not matching the task_status condition

I get the rows which are not with status 0 or 3 because it match the task_assigner with other rows and shows the result.

Is there any workaround for this ?


texts: Why is optimistic locking faster than pessimistic locking?

texts:
Both forms of locking cause a process to wait for a correct copy of the record if its currently in use by another process. With pessimistic locking, the lock mechanism comes from the DB itself (a native lock object), whereas with optimistic locking, the lock mechanism is some form of row versioning like a timestamp to check whether a record is "stale" or not.

But both cause a 2nd process to hang. So I ask: why is optimistic locking generally considered faster/superior than pessimistic locking? And, are there are use cases where pessimistic is preferred over optimistic? Thanks in advance!


texts:
I am working with Microsoft Office Professional Plus 2010.  What I am trying to figure out is how to merge all characters for each DOMAIN field into one row, for example:

Q01                         DOMAIN      Q32
-----------------------------------------------
http://redbull.example.com  example.com F
http://www.example.com      example.com B

What I would like to accomplish through a query is to take the DOMAIN field and create a view which merges the "Q32" into one row, for example:

DOMAIN      Q32
----------------
example.com B F

Is this possible through a design query or would I have to write a SQL statement?  I tried the following SQL statement to create a UNION with two different tables, though I still didn't get the merge displayed correctly.

SELECT DOMAIN, Q32
FROM tblMERGE
UNION
SELECT DOMAIN, Q32
FROM tblASSET;


texts: Is SQL Server on Mac equally well performing as it is on Windows?

texts:
My friend decided to learn SQL Server and T-SQL. However, he has a Mac machine.

It is my understanding that SQL Server's primary use case was to be run on Windows.

What potential issues may he run into with SQL Server on Mac?

For example how well does SQL Operations Studio work, does it miss features? Is it reliable?


texts: Azure SQL Elastic Pools - When to Create a new one versus upping the eDTU?

texts:
I've tried to Google looking for others with the same question for about an hour now and can't find anything close, so hoping someone here might be able to offer some insight!
I'm a many-hat admin with a small software company the provides SaaS service solutions to our clients, we use Azure SQL, with Elastic Pools to host around 100 individual customer databases currently that run quite happily on a Standard Tier 100 eDTU Elastic Pool. Utilisation through the day runs between 60-80% which for our application is quite a comfortable buffer in case one of the clients should go crazy with a report query or bulk data load.
We are about to bring another 100 or so clients currently hosted on legacy VMs running SQL classic over into Azure SQL as well.
My questions is: Do I place them in the current elastic pool and up the eDTU to 200, or do I create a new separate 100 eDTU elastic pool and place them there?
Database size and Min/Max DTU per database settings, and the marginal difference in costs aren't considerations. So given that all the relative resource limits effectively double between 100 and 200 eDTU (concurrent workers/connections/sessions etc), is it just a question of how they are logically grouped?
What considerations would go into this choice? Is there something I'm missing?


texts: Duplicate non-clustered index on Primary Key, can I drop one of them?

texts:
Looking over on of our older databases I noticed that several large tables had duplicate indexes on the Primary Key.

Both indexes are Non-clustered, Unique and only includes the PK-column. The clustered index is on a different column

Below is the SQL to create the indexes

ALTER TABLE [dbo].[TableName] ADD  CONSTRAINT [PK_TableName_1] PRIMARY KEY NONCLUSTERED
([id] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]

and

CREATE UNIQUE NONCLUSTERED INDEX [PK_TableName] ON [dbo].[TableName]
( [id] ASC )
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [FGIndex]

Both these statements will create a index that to my eyes looks like duplicates.
The PK-index will be indicated with the "yellow key" in the indexlist in SSMS.

Is it safe to drop the the index? According to statistics the 2nd index is often the one that the Query Plan Engine chooses. The second index also uses less space.

Thanks for any input.


texts:
I have a personal computer where I have different instances of SQL server running (developer edition) with both integration services and analysis services (one in tabular and one in multidimensional). I use it for practice and to improve my skills. The start mode in Configuration Manager is "Automatic". So I have two instances each with both SSIS and SSAS. When I am not using SQL Server will these services use a lot of resources on my computer by simply running in the background?
Thanks


texts: Does MySQL have a more private and complete catalog other then INFORMATION_SCHEMA?

texts:
Most databases provide the standardized INFORMATION_SCHEMA, however most also provide something else,

In PostgreSQL we have the system catalog which is pg_catalog
In SQL Server, you have the System Base Tables

Does MySQL expose anything other than INFORMATION_SCHEMA? Specifically, the question was inspired by trying to find if there is a table that is responsible for internal function resolution, like pg_catalog.pg_proc which tells me for instance all the GIS functions my database has. If that was in MySQL I could address this comment. But, that aside, I just want to know if there anything more than INFORMATION_SCHEMA. That is the question.


texts: Indexes messed up / slow after running mysqlimport

texts:
I'm doing a restore using mysqlimport by iterating over files generated by mysqldump --tab and the import is very fast but performance seems to be lagging after the import.
Does mysqlimport neglect to build the table indexes or is there something else going wrong here?
Per request in comments, here's an example of table schema for one of the problem tables:
`CREATE TABLE `product_search` (
  `product_id` int NOT NULL,
  `cost` decimal(12,2) NOT NULL,
  `search` text NOT NULL,
  `active` tinyint unsigned NOT NULL,
  PRIMARY KEY (`product_id`),
  KEY `active` (`active`),
  KEY `cost` (`cost`),
  FULLTEXT KEY `search` (`search`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8`


texts: What learning resources are there for part-time DBAs?

texts:
Luckily, this isn't a current issue for me, as in my current workplace we're pretty well supplied with DBA knowledge in the team.  However, in small development teams, it's not uncommon in my experience for one of the team to be nominated as the de-facto DBA.  For a small team there's rarely enough work for a full-time DBA (and/or there are DBAs, but the production DBAs don't want to get involved with supporting the dev/test environments), so a tester or programmer will be expected to pick up that role, and I've seen people getting suddenly landed with "hey, Joe's leaving, so you're now our new DBA.  No, we have no training budget, you'll have to teach yourself. Have fun!"

What resources can you recommend for someone who finds themselves suddenly in the position of having to get up to speed with a development DBA role?  What basic tasks should they aim to tackle first, just to keep things ticking over?


texts: how to create and insert into a table in an anonymous block

texts:
I have PL/SQL anonymous block that creates a table (using execute immediate) and then inserts data into the table.

When the block compiles, it gives me an ORA-00942: table or view does not exist error.

Well, no, it doesn't exist, but it will be created before the insert occurs.  If I create the table before compiling, it will work.

How can I handle this?


texts: How to Create Database Audit Specification for a column?

texts:
I am creating SQL Server 2008 R2 Database audit Specification
for tblPerson.SSN

I selected

Audit Action Type: Update
Object Class: Object
Object Name: tblPerson
Principal name: Public

It captures all changes to the table. How could I see just one column?

I could filter my column out

select statement,* from fn_get_audit_file ('q:\SQLBackup\*',null,null)
where statement like '%SSN%'


texts: How to find out CPU utilization per Oracle Database on Linux?

texts:
For capacity planning on existing RedHat servers hosting Oracle Databases, I was asked how to find out the CPU utilization per database.

Thought it would be as simple as

ps -eo pcpu,comm |grep -i oracle_dbname

But I'm not sure if this is the right approach.


texts: PostgreSQL High Availability/Scalability using HAProxy and PGBouncer

texts:
I have multiple PostgreSQL servers for a web application.
Typically one master and multiple slaves in hot standby mode (asynchronous streaming replication).

I use PGBouncer for connection pooling: one instance installed on each PG server (port 6432) connecting to database on localhost. I use transaction pool mode.

In order to load-balance my read-only connections on slaves, I use HAProxy (v1.5) with a conf more or less like this:

listen pgsql_pool 0.0.0.0:10001
        mode tcp
        option pgsql-check user ha
        balance roundrobin
        server master 10.0.0.1:6432 check backup
        server slave1 10.0.0.2:6432 check
        server slave2 10.0.0.3:6432 check
        server slave3 10.0.0.4:6432 check

So, my web application connects to haproxy (port 10001), that load-balance connections on multiple pgbouncer configured on each PG slave.

Here is a representation graph of my current architecture:

This works quite well like this, but I realize that some implements this quite differently: web application connects to a single PGBouncer instance that connects to HAproxy which load-balance over multiple PG servers:

What's the best approach? The first one (my current one) or the second one? Are there any advantages of one solution over the other?

Thanks


texts:
I wrote this simple update query:

update foo set test ='褠ఓ�ᵂ뱗텬㰗' where id = 1

[test] is an nvarchar(16) column.  It's going to contain an encrypted value.

In SSMS (10.50) I've set the font for grid results and text results to Lucida Sans Unicode.  The glyphs (褠ఓ�ᵂ뱗텬㰗) are displayed in the query editor window. But the grid results and text results windows always show simply question marks '????????" instead of 褠ఓ�ᵂ뱗텬㰗.

I closed and opened SSMS after making the font changes in Tools ->Options.

Any suggestions would be appreciated.  I normally won't need to make any sense of the contents of this column, but when testing to make sure the client app is sending through the correct data, it would be helpful to see something other than question marks.


texts: How to change the replicate_*_table variable without modifying my.cnf?

texts:
what is the syntax of replicate_*_table without having to modify my.cnf file?
and do i need to restart mysqld for changes to be applied


texts: Using non-replicated read/write databases on a MySQL slave server

texts:
I have a MySQL slave server that has two databases being replicated from a master. What I'd like to do is add a third database that I will exclude from replication and use as a read/write database. Basically this is a database to store local session variables, so I won't have to run MySQL on the application server in this region.

So my questions are:

Can I do this?
If I can, is it a good idea, or should I keep the slave strictly to read only databases (beyond practical considerations)?

Any answer much appreciated, technical explanations and links to more reading would be great. I've found it difficult to Google much on this topic.


texts: Postgresql condition on subquery results

texts:
This is a new question based on the solution to my previous question

I have the following tables:

users

int (pk) user_id
date last_visit
int (fk) local_site

sites

int (pk) site_id

site_stats

int (fk) site_id
int total_users
int monthly_users
int weekly_users

And I am using this query to update the user_stats table

UPDATE site_stats
SET
    total_users = totalUsers,
    monthly_users = monthlyUsers,
    weekly_users = weeklyUsers
FROM
(
    SELECT
        u.local_site AS site,
        count (u.*) AS totalUsers,
        count(CASE WHEN u.last_visit >= now()::DATE - 30 THEN 1 END) AS monthlyUsers,
        count(CASE WHEN u.last_visit >= now()::DATE - 7 THEN 1 END) AS weeklyUsers
    FROM users u
    GROUP BY u.local_site
) AS subquery
WHERE site_stats.site_id = subquery.site ;

This works fine, except that if there are 0 users linked to a site in the time frame then no update will occur, so the table col will keep the previous value rather than showing 0.

What would be the best way to add a condition so that any sites with 0 linked users is updated with 0?

My first guess was to use a CASE in the WHERE clause?


texts: Linked Server Between SQL Server and iSeries is rounding decimals

texts:
We just set up a new SQL Server 2008 database, and I got the link set up between our server and an iSeries As400 server that we get some of our data from.

The problem I'm having is that it is rounding down any floating numbers, which is a pain because we're working with dollar amounts a lot of the time.

select * FROM openquery(NADS, 'select CMD, 1.23 Type from CREDD')

On our older server this returns the CMD column, and a column called Type with 1.23 all the way down. On the new server type is just 1.00

So far as I can tell, the link part is set up the same. The IBM i Access for Windows ODBC Setup is mostly the same, but there are some differences between versions that I'm not sure about.

Edit:
In case it matters, For the provider I'm using Microsoft OLE DB Provider for ODBC Drivers

Edit2:

provider options are the same

Linked Server properties:

iSeries Access for windows ODBC setup appears to be the same


texts: SQL71561 Error on Schema compare after initial import from database

texts:
I have created a new sql server project with SSDT 2012, then I went to import an existing database and it created all my scripts. After that I did a schema compare between the project and the database I just imported the objects from. And I got tons of errors, most of them SQL71561 "contains unresolved reverence to an object. Either the object does not exist or the reference is ambiguous because it could refere to any of the following objects.....

Am I expecting too much from SSDT that after an import a schema compare shouldn't return any errors ?


texts: Access oracle database from control file

texts:
When I asked a client for a copy of his database he sent me a .ctl file, it seems it is a control file of an Oracle database.
I have no experience in Oracle, I'm a SQL Server guy.
I have installed an Oracle XE 18 instance in a test server. What do I have to do to  connect to this database an access the tables? Is it possible to do so only having this .ctl file or do I need more files?


texts: How to efficiently combine user authentication with token authentication?

texts:
What is the most efficient approach of authentication, considering an user based authentication system combined with a token based authentication system?
For this question please consider the following tables:
create table users (
  id int generated always as identity primary key,
  username text not null unique,
  password text not null
);

create table tokens (
  id int generated always as identity primary key,
  user_id int not null references users(id),
  access_token text unique default generate_random_token(),
  refresh_token text default generate_random_token(),
  updated_on timestamp with time zone default current_timestamp,
  created_on timestamp with time zone default current_timestamp
);

As far as I know to create an user and allow it to authenticate the following steps are mandatory (explained with query statements):

Optional: Create an user record.
insert into users (username, password) values ('foo', create_password_hash('bar'));

Check if the user can authenticate itself and remember the user's ID:
select * from users where username = 'foo' and password = validate_password_hash('bar', password);

Check if the user has an existing token and hand over the access_token:
select access_token from tokens where user_id = 123;

Optional: Create a token if the user doesn't have one yet:
insert into tokens (user_id) values (123) returning access_token;

If my assumption for the steps to be taken is right, then I wonder how to minimize these four statements into a single one or have at least some guidance how this could be done more efficient. Also I could imagine that my approach for this use case incorrect and that there is an other more efficient approach, please let me know.

BONUS
Perhaps your approach could also cover the process of refreshing a token. Considering the approach explained above the refresh should only occur when a token is found at the third step and according to the updated_on timestamp the token is "expired".


texts: Cannot kill query "Optimize Table" - the temporary #sql data keeps increasing!

texts:
First of all, I apologize if a similar question has been asked, but I am very not familiar with mySql and did my best to get used to some terms by reading wiki pages, etc and also look up topics on StackExchange.

We have a database running on MariaDB v10 (on Windows 10) We recently backed up a database full of experimental data and decided to run "Optimize Table ...", because after deleting many temporary data, fetching data became much slower and we suspected disk fragmentation. So we ran the query on HeidiSql client. But we realized that the remaining space in the drive was not enough for "Optimize Table", when it tries to copy the remaining data, so we killed the query. However, we noticed the disk space kept reducing, and noticed that the temporary #sql-... file was still increasing in size!

We tried things like "mysqladmin shutdown" to no avail, so ended up just killing the "mysqld" process on Windows Task Manager.

What I would like to understand is, why does the temporary #sql file keeps building up, even when I killed the query that started it?

EDIT: I found this blog post saying that the "kill" command does not always happen immediately. It does consider the example of altering table. But in my case, it was not happening for at least half an hour, and that seems extreme.


texts: How to run OPTIMIZE TABLE on a 3-node cluster?

texts:
I've my hands on a 3-node Percona XtraDB Cluster where, according to mysqlcheck, some tables are corrupted (some indexes contain the wrong number of entries):

mydb.mytable
Warning  : InnoDB: Index 'foo' contains 1393929 entries, should be 1393918.
Warning  : InnoDB: Index 'bar' contains 1393921 entries, should be 1393918.
error    : Corrupt

What is the best practice to run OPTIMIZE TABLE on a cluster?

I've done some experiments in a test environment without users, and it appears that an OPTIMIZE TABLE on a node does not automatically propagate its effect to the other nodes. This is consistent with the fact that this command modifies the indexes and the table's storage space, not its contents or its definition.

What could be the drawbacks in running the command in a production environment in each node, letting it complete before running it in the following node?
What would be the effect on users, considering that MySQL (and Percona XtraDB Cluster, as far as I know) do not support distributed table locks? Would this leave the cluster in a inconsistent state?


texts: How can you effectively visualise a complex relational database?

texts:
I have an Access database which contains all the data from an open-source Japanese dictionary, JMdict. The database contains 24 linked tables, so reading it is complicated. The reason for this is that nearly every type of information relating to a single "Entry" can have more than one value. Here is the outline of the database, in terms of how the tables relate to one another:

Entry

Keyword

Keyword_Orthography
Keyword_Priority

Reading

Reading_Orthography
Reading_Priority
Reading_ReadingToKeywordRestriction

Sense

Sense_Antonym
Sense_CrossReference
Sense_Dialect
Sense_Field
Sense_Gloss
Sense_GlossType
Sense_LanguageSource
Sense_Misc
Sense_PartOfSpeech
Sense_SenseRestrictedToKeyword
Sense_SenseRestrictedToReading
Sense_Translation

Every table has a one-to-many relationship with the tables below it in the hierarchy - each Entry can have multiple Keywords, Readings and Senses, every Keyword can have multiple Keyword_Orthography and Keyword_Priority values, and so on.

So visualising even a single Entry seems like a complicated task. I could (probably) write a query that puts all of the information in a single table, but it will lead to massive redundancy if, for example, I'm displaying the same "Entry" five times in order to display all five of the "Sense_Misc" values which relate to it. Which, I gather, is the whole point of using a relational database instead of a spreadsheet.

Which leads me to my question:

In a situation like this, how do you effectively visualise all data across a complex database, not just one or two tables?

The best I can do right now is write individual queries to display, for example, each Keyword for a single Entry, or each Reading_Orthography for a single Reading. But I don't know what the best practice would be if I want to see the whole of the database across all of the tables.

What I really need is something that can neatly sum up a single "Entry" including all of the related information, and hopefully some way of summing up all of the Entries in a single table. Is there a method for concatenating values in a single query, for example?

I realise my question seems broad, so if the specific question about my situation seems unanswerable, I'll stick to the more general question: what is the best practice for visualising a relational database comprising of many tables in one place?


texts: Upgrade SQL Server 2005 Enterprise to SQL Server 2008 R2 Standard

texts: I understand that this is not a supported path, but does anyone have any insight about how to accomplish this? I can't seem to find any documents on the subject. I need my QA (2005) to match my Production (2008R2). Just joined this company and this is one of my first tasks...


texts: Output in SQL Profiler has results with Database ID as NULL

texts:
I ran profiler to filter out certain applications connecting an environment but the results has application for Database_id "NULL"..
Am I missing anything ?

Also another question , does all the filters applied in profiler are with AND condition / OR condition ?

Version used : 2008R2


texts: How can I optimize a mysqldump of a large database?

texts:
I have a symfony application with an InnoDB database that is ~2GB with 57 tables.  The majority of the size of the database resides in a single table (~1.2GB).  I am currently using mysqldump to backup the database nightly.

Due to my comcast connection, oftentimes if I am running a dump manually my connection to the server will timeout before the dump is complete causing me to have to rerun the dump.  [I currently run a cron that does the dump nightly, this is just for dumps that I run manually.]

Is there a way to speed up the dumps for the connection timeout issue, but also to limit the time the server is occupied with this process?

BTW, I am currently working on reducing the size of the overall database to resolve this issue.


texts: Database growth causing issues sending backup over network

texts:
We take a full database backup on our primary SQL Server, and send that file over our network to a remote site where it is recovered on a backup SQL Server.  We use this backup database for slow queries that don't need the latest data, and, as a backup in the event of a complete failure of the primary database.

As the file grows it is causing problems with the available bandwidth.  When the file transfer takes place, it sucks down just about all the bandwidth, which is OK.  However as the file size grows (10+ gig) the amount of time that this transfer takes is becoming a problem because it is interfering with other things.

Is there some other strategy we can use to send our backup to a remote server?


texts: Updating total using values from previous row - SQL Server 2016

texts:
I have a table that depends on the previous row’s values for its new total. We have discovered that a couple of years ago a value was incorrectly altered and has subsequently made all following values incorrect.  I have inherited a formula that uses the previous date to calculate but there appears to be dates missing in the table and at this point the formula stops. I would like to make the update use the previous row rather than the date. I have tried using LAG but had issues using it with an UPDATE.

This is an example of the type of data I am using

|  Date   |Value1|Value2|Value3|Total
--------------------------------
|20151112 |  2   |  2   |  3   | 20

I need to add Value1 + Value2 + Value3 * Previous Day Total. The answer to this will update the following row's total. As mentioned I need to use an UPDATE because there is existing data in the total, but it's currently incorrect.

The results I would like to achieve are in the Table below. Please be aware that dates are not sequential so I can't use date as a condition. An answer with LAG would be great.

|  Date   |Value1|Value2|Value3|Total
--------------------------------
|20151112 |  2   |  2   |  3   | 20
|20151114 |  2   |  3   |  1   | 140
|20151115 |  1   |  1   |  1   | 420
|20151117 |  1   |  3   |  1   | 2100
|20151119 |  2   |  1   |  3   | 12600

Example - for the 20151114 - 2 + 2 + 3 * 20 (Total from the previous row) = 140


texts: Changed a user name in MySQL, but user is still able to login using previous user name

texts:
I created a user with user name user_dev. Then created a database sampledb and granted some privileges to this user on this database. Then I decided to edit username and changed it to user_local, and granted same privileges to this new name on same database.
Now both user_dev and user_local are able to login, but user_dev has no access to sampledb and privileges for user_local are correct.

SELECT `user` FROM `mysql`.`user`

does not show user_dev.
My question is, why user_dev is still able to login? Should I take any more steps to prevent this user from loging in?


texts: Is there such thing as an Availability Group failover trigger?

texts:
Is there such thing as a trigger for Availability Group failovers?

I want a certain action to happen when an AG fails over. Specifically, I want to turn on a database setting (turning on RCSI). I want to do this on a failover in order to minimize disruption to 24/7 workloads and scheduled maintenance windows are hard to come by.

I know that sp_procoption can be used to mark procedures as startup procedures.
This seems like it could work for failover clusters, but not for Availability Groups.

I did consider adding an alert (sp_add_alert) on message_id=26069 in order to respond to failover actions with a sql agent job. But this seems less direct and in practice it seems slow


texts: Rewriting query with subquery to improve performance

texts:
My query looks like that

SELECT topics.name, topics.topic_id, topics.creator,
( SELECT COUNT(*) FROM answers
WHERE answers.topic_id = topics.topic_id)
AS replies FROM topics
ORDER BY topics.bumped DESC, topics.date DESC LIMIT 500

However it takes 0.6s, I'm guessing that's because of the subquery that counts answers. Any other way to achieve this effect with better performance?


texts:
I'm confused how should I use -b parameter of sqlcmd. I'm trying to write simple automation script for my DB and wanted to stop execution in case of any errors. I'm using SQLServer 2008r2.

For testing I'm calling simple script

sqlcmd -b -i test.sql

content of test.sql:

PRINT 'test1';
RAISERROR ('Error raised', 15, 15);
PRINT 'test2';

I expected that after first print I would see error msg and script would stop. But every time second print is executed and I see following output:

  test1 Msg 50000, Level 18, State 15, Server TOMEK, Line 3
  Error raised test2

What I'm doing wrong?


texts: Mysql shutting down during a mysqldump of a specific database

texts:
I have a server which is running Centos 6.6 with Cpanel.  I have been attempting to do mysqldump backups on individual databases.  All of the databases are under 5MB except for one.

The database that is around 250MB causes a mysql shutdown in the middle of running a mysqldump.  The other databases work fine during a mysqldump.

My question is could this be due to a corrupted database?  What should I do to start troubleshooting this?

Is there another way to backup this database other than mysqldump.  I just need one copy so I can move the troubled database to another server and remove it from this one.

Here is my.cnf

[mysqld]
table_open_cache=100K
max_connections=500
thread_cache_size=4
query_cache_size=8M
query_cache_limit=1M
query_cache_type=1
innodb_file_per_table=1
default-storage-engine=MyISAM
open_files_limit=10000
max_allowed_packet=268435456
innodb_buffer_pool_size=123731968

Let me know if you need more information.


texts: Rebuild large Oracle 10g index with insufficient TEMP space?

texts:
One of my (Oracle 10g) tables has grown over time to about 840 million rows. An index (DATE, NUMBER(38)) on it occupies 38GB at present. I'd like to change the index (I think it could make good use of COMPRESS, for example) but it fails with errors relating to TEMP getting full - it's 4GB at present.

Is there a way to build an index that will be larger than TEMP? I realise I could export the table, truncate it, create new and interesting indices and then reload, but that goes way beyond my capacity for pain...


texts:
I need to learn how to create a dimensional modelling for an organisation that do not has a BI tool and BI model.

Where can I learn more about BI development that should be used in a company?

IT's development and NOT maintainence I need to learn. The tool I'm using is Microsoft BI.


texts: DB2: Cannot use RUNSTATS/REORG in trigger definitions?

texts:
CREATE TRIGGER MY_TRIG AFTER INSERT ON MY_TBL FOR EACH ROW
    BEGIN
        IF SOMECONDITION THEN
            DELETE FROM MY_TBL WHERE PK IN (SELECT PK FROM MY_TBL WHERE SOMEOTHERCONDITION FETCH FIRST 50 ROWS ONLY);
            RUNSTATS ON TABLE MY_TBL WITH DISTRIBUTION AND DETAILED INDEXES ALL;
            REORG TABLE MY_TBL;
            RUNSTATS ON TABLE MY_TBL WITH DISTRIBUTION AND DETAILED INDEXES ALL;
        END IF;
    END#

Executing this through the control centre of DB2 9.4.7 (with # as terminating char) this gives me error SQL0104N:

An unexpected token "RUNSTATS" was found following "T 50 ROWS ONLY;
            ". Expected tokens may include: "COMMENT

I do not understand why. Am I not allowed to use commands such as RUNSTATS/REORG inside trigger definitions? Executing the RUNSTATS command separately works just fine.

------Edit------
A bit more about what I'm trying to achieve here:
The table in question contains BLOBs and many of them. In order to limit the disk space used by this table I'd like to set a limit on how big the table may get. So I use the information from the xxx_OBJECT_P_SIZE columns in SYSIBMADM.ADMINTABINFO to determine the current size of the table and if it's beyond some limit I simply delete the oldest 50 records of the table. The more records I delete on such an occassion, the less frequently reorg/runstats would have to be executed. So the statements are not being executed for each row inserted - only the condition SOMECONDITION would be evaluated every time.

Is there a better way of achieving this than by using a trigger?
------Edit------


texts: Electricity off while shrinking database, how can I recover my database?

texts:
While i execute shrink database, the electricity has been gone down, I can't access my database now, it is (In Recovery), how can i recover it?? Note: it is a very huge database: 25 GB.

EDIT:

My question on serverfault.com: https://serverfault.com/questions/395067/electricity-gone-off-while-shrinking-database-how-can-i-recover-my-database


texts: Is having SQL Server express database on client better than querying data on a remote server?

texts:
I'm planning to create a SQL Server Express database on each client machine that will sync with the remote server's data. What I'm trying to achieve is to have a faster processing and querying of data wherein the client application connects to its local db rather than directly connecting to the remote server.

Do you think that this is a good approach? Or should I just stick to the client-server approach wherein all I need to do is have 1 remote db server where the clients connect to it?


texts: I have a CSV file that contains a lot of integer values. Is it better to insert these individual values in bulk as in a full transaction or should I be inserting the CSV file itself into the MySQL?


texts: SQL Server : database free space vs data file free space

texts: I don't understand well why the available space in database is 0% while in file is 98% available. Can you explain or advise? (SQL Server 2019, SSMS the newest, one datafile)


texts: How do I query INFORMATION_SCHEMA.COLUMNS on Amazon RDS?

texts:
I received a list of column names from another team. To ensure all field column names are valid inside a specific schema, I put them into a temporary table. I'm now wanting to see if they exist. When I try to join with information_schema.columns, I received errors below:

ERROR: 0A000: Specified types or functions (one per INFO message) not supported on Redshift tables.
Column "c.column_name" has unsupported type     "information_schema.sql_identifier".
Column "a.*" has unsupported type "pg_attribute".
Column "t.*" has unsupported type "pg_type".
Function "format_type(oid,integer)" not supported.
Function "format_type(oid,integer)" not supported.
Function "has_table_privilege(oid,text)" not supported.
Function "has_table_privilege(oid,text)" not supported.
Function "has_table_privilege(oid,text)" not supported.
Function "has_table_privilege(oid,text)" not supported.

I tried to cast column_name into different types, but had no luck. Would anyone please advise what's wrong and how I can achieve this goal?


texts: How can we restrict collections on specific shards of Mongodb while using Tag Awareness Sharding?

texts:
We have a Mongodb cluster which has 10 shards (0-9) and two different applications supposedly as APP1 and APP2. Both APP1 and APP2 have different data model and shard keys.

We want that to assign shards 0 to 5 to APP1 and 6-9 to APP2. For that we have created tags for each shards and have assigned these tags with a range to each application using the following commands:

sh.addShardTag('shard0', 'APP1_V0')
sh.addTagRange("test.collectiontest", {"_id.s":0,"_id.i" : MinKey}, {"_id.s":0, "_id.i" : MaxKey}, "APP1_V0")

We have added tags ranges similarily on each of shards based the shard key of the applications.

The problem that we are facing while creating collection, the primary shard i.e assigned to the collection is different from tags assigned to the collection. For eg, App1 collection is tagged to shards 0-5 but when i'm a creating the collection , primary shard assigned to the collection is shard7.

I checked the primary using the sh.status() command.

Can we control this behaviour i.e while creating the collection assign it to the specific shard.

Mongodb Version : 3.4.2


texts: isql*plus crashes in 9i when multiple user execute query simultaneously in oracle 9i

texts:
In my workplace Multiple users are using isql*plus(oracle 9i) to access single database through server url .But after some time isql*plus crashes and its services needs to started.

Its causes  a lot of  time waste and is also decreasing the productivity please help.
Sever is a desktop with 2 GB of RAM and 2.5Ghz AMD processor
No of user 25.

The way database is accessed isql*plus
isql*plus is insatalled in pc in which main database access is provided (like proxy server ). here is the main problem and you can say isql*plus services crashes in proxy server.
If services runs correctly then its routed to database and database gives result to proxy server.
user see the result in isqlplus browser(url =ip address of proxy ).

clients make request using


texts: How to upgrade Mysql 5.5 to 5.6 without data loss on aws-ec2?

texts:
MySQL server (5.5) is running on aws-ec2 instance with following linux details
NAME="Amazon Linux AMI"
VERSION="2015.09"
ID="amzn"
ID_LIKE="rhel fedora"
VERSION_ID="2015.09"
PRETTY_NAME="Amazon Linux AMI 2015.09"
ANSI_COLOR="0;33"
CPE_NAME="cpe:/o:amazon:linux:2015.09:ga"
HOME_URL="http://aws.amazon.com/amazon-linux-ami/"

Since Django 2.2 doesn't support Mysql 5.5
https://docs.djangoproject.com/en/2.2/ref/databases/#mysql-notes
. I want to upgrade MSQL to 5.6 .

We don't have backup server and we don't want new server  .
Creating sql dump , removing MySQL 5.5 then installing MySQL 5.6 and importing dump will it work  ? Since server has 100 GB of data importing the dump will be a long process and we will have considerable amount of downtime .There can be some issue with data while importing dump in 5.6 .

Is there a way to upgrade MySQL without data loss and less downtime ?


texts:
I've been looking into using TDE in our SQL Server 2014 environment and have a few questions. I think I have a fairly solid grasp on the numerous "how-to's" that are out there on initially implementing TDE.  One thing I'm not finding is what state the DB is left in if you restore it to another instance.

For example, I encrypt a DB on one instance, then restore it to another after creating a master key and TDE certificate with the following commands on instance B:

/* Create key in master on second server */
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'

 /* Re-create server certificate in master database
by using the original server certificate backup file.
The password must be the same as was used when the
certificate backup was created. */

    CREATE CERTIFICATE TDE_Dev_Cert
        WITH SUBJECT = 'TDE Certificate'
        FROM FILE = 'C:\certfile'
        WITH PRIVATE KEY (FILE = 'C:\privatekeyfile'
        DECRYPTION BY PASSWORD = 'password';

After a database that has been encrypted with TDE on instance A is restored to instance B, is the database fully decrypted at that point and no longer using TDE, or is the certificate simply allowing TDE to continue to function normally?  The Encryption/Decryption flag on the certificate is what is confusing me.  Thanks in advance.


texts: Percona Xtradb Cluster not accepting options on command line

texts:
I am using Percona Xtradb Cluster 5.6 and trying to start my cluster through a donor node via command line and I am getting this error:

CentOS 7:

  systemctl start mysql --wsrep-sst-donor=srv1

  systemctl: unrecognized option '--wsrep-sst-donor=srv1'

I am running 3 nodes cluster. I have started my first node-A with bootstrap, then started second node-B which synced with node-A and now i want my third node-C to sync with the first one node-A as well, but unfortunately it was getting sync with node-B.
how can i pass an option for donor on command line in centos7?


texts: How do I determine why my stored procedure is getting deadlocked?

texts:
We have a stored procedure that our application uses to query data (no write operations are performed). I have the complete staging database copied to my local version of Sql Server developer edition.  The stored procedure locally runs perfectly fine and gives me results in less than a second.  When I run it on our staging system the stored procedure gets suspended and sits there.

I cannot figure out why this is getting deadlocked and I feel stuck.

When I run exec sp_lock I see a LOT of entries for a specific spid with all different object ids, and all the locks are of type TAB with different object ids (probably makes sense as the query returns about 30 fields, at least half of which are subqueries).

When I run exec sp_who2 I see that SPID keeps going between SUSPENDED and RUNNABLE states, with ever increasing CPUTime and DiskIO values.

I'm not sure where to go from here.  I can almost guarantee that the tables the stored procedure is using is not being used by anything else, as this system isn't live.  Also, other stored procedures querying data over the same tables run flawlessly.  How can I identify the source of the deadlock?


texts: CASE in WHERE clause - retuns a single-row result instead of 4 rows?

texts:
Everyone,
I do not want to bother you with the whole tables schema so I will paste a couple of screenshot only so that my question becomes clear - I need to show all projects that are related to EmployeeID = 24, if the project has started during or after year 2005, then the ProjectName field should be 'NULL', otherwise the ProjectName should show the actual value.

Once I solve it this way, I receive just a single result in MS SQL Management Studio

SELECT Employees.EmployeeID, Employees.FirstName, Projects.[Name] AS [ProjectName], Projects.StartDate
FROM Employees
INNER JOIN EmployeesProjects ON EmployeesProjects.EmployeeID = Employees.EmployeeID
JOIN Projects ON Projects.ProjectID = EmployeesProjects.ProjectID
    WHERE Employees.EmployeeID = 24
        AND Projects.[Name] = CASE
            WHEN Projects.StartDate = '2005' THEN NULL
            WHEN Projects.StartDate > '2005' THEN NULL
            ELSE Projects.[Name]
            END

If I run the above code partially:

SELECT Employees.EmployeeID, Employees.FirstName, Projects.[Name] AS [ProjectName], Projects.StartDate
FROM Employees
INNER JOIN EmployeesProjects ON EmployeesProjects.EmployeeID = Employees.EmployeeID
JOIN Projects ON Projects.ProjectID = EmployeesProjects.ProjectID
    WHERE Employees.EmployeeID = 24

there are 4 results as per the attached screenshot:

This solution is suggested and gives the full mark:

SELECT E.EmployeeID,FirstName,
  CASE
   WHEN P.StartDate > '01/01/2005' THEN NULL
   ELSE P.NAME
  END
  FROM Employees AS E
INNER JOIN EmployeesProjects AS EP ON EP.EmployeeID = E.EmployeeID
INNER JOIN Projects AS P ON EP.ProjectID = P.ProjectID
WHERE E.EmployeeID = 24

However, I want to ask why my solution shows just a single row, what is my mistake in the way query sequence? Is not it possible to retrieve the same by first doing the joins and then using a WHERE clause which includes a CASE?

Appreciate your feedback!

p.s. in case you need the script (schemas, insert), just let me know and I can add them as well.


texts:
I'm being prohibited from dropping any table from a db because of this trigger. I can't figure out why it is applying. The table(s) in question are not part of cdc or replication (neither has EVER been enabled on this instance, much less this db)

Nor are the tables in question showing up in...

OBJECT_ID('dbo.sysarticles') IS NULL OR OBJECT_ID('dbo.syspublications') IS NULL OR OBJECT_ID('dbo.sysextendedarticlesview')

any of those tables (from the trigger)

Google is no help, has anyone ran in to this before?

edit - entire trigger text

/****** Object:  DdlTrigger [tr_MStran_droptable]    Script Date: 12/16/2016 12:32:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [tr_MStran_droptable] ON DATABASE FOR DROP_TABLE AS

                            SET ANSI_NULLS ON
                            SET ANSI_PADDING ON
                            SET ANSI_WARNINGS ON
                            SET ARITHABORT ON
                            SET CONCAT_NULL_YIELDS_NULL ON
                            SET NUMERIC_ROUNDABORT OFF
                            SET QUOTED_IDENTIFIER ON

                            DECLARE @EventData XML
                            SET @EventData=EVENTDATA()

                            IF OBJECT_ID('dbo.sysarticles') IS NULL OR OBJECT_ID('dbo.syspublications') IS NULL OR OBJECT_ID('dbo.sysextendedarticlesview') IS NULL OR EXISTS(
                               SELECT 1
                                 FROM @EventData.nodes('/EVENT_INSTANCE') AS R(event_instance)
                                WHERE LOWER(event_instance.value('SchemaName[1]', 'sysname')) IN (N'sys', N'cdc')
                                   OR LOWER(event_instance.value('ObjectName[1]', 'sysname')) IN (N'sysextendedarticlesview', N'sysarticles', N'syspublications')
                                   OR event_instance.value('ObjectName[1]', 'sysname') LIKE N'#%'
                            ) RETURN

                            EXEC sys.sp_MStran_ddlrepl @EventData, 5
    GO

    ENABLE TRIGGER [tr_MStran_droptable] ON DATABASE
    GO


texts: SQL: Query to insert records into the table if there is not even one record with certain condition

texts:
I have a table train_statuses.
With the schema as below:
CREATE TABLE public.train_statuses (
    id uuid NOT NULL,
    status text NOT NULL,
    updated_at timestamp without time zone NOT NULL
);

Indexes:
    "train_statuses_id_updated_at_key" UNIQUE CONSTRAINT, btree (id, updated_at)
    "idx_train_statuses_id_updated_at" btree (id, updated_at)

This table can have multiple entries for the same id.
The status can be one among these values : 'cancelled', 'queued','executed', 'failed', 'succeeded'
Now I need to write a query that inserts entries into train_statuses for all ids that has their status only as either queued or executed with status as 'cancelled'
I came up with this query
WITH ts AS (
        SELECT
            DISTINCT ON (ts.id) ts.id, ts.status
        FROM train_statuses ts
        INNER JOIN train_statuses t ON ts.id = t.id
        WHERE
            ts.status IN ('queued','executed')
            AND ts.status NOT IN ('failed', 'succeeded','cancelled')
    )
    INSERT INTO train_statuses (id, updated_at, status) SELECT id, now(), 'cancelled' FROM ts

This technically works.
But I feel there would be a more optimal way to get this done.
Joining train_statuses again with itself doesn't seem that great since the table size is around 150GB and we don't have index on status.
Please go through and let me know if there is a better way to get the desired result.
Thanks
Note: If there is any better title for this question please add an edit suggestion.


texts: How to access a SQL Server database from other computer connected to the same workgroup?

texts:
I have created a C# application which uses a SQL Server database. I have other computers connected to me and to each other in a workgroup. I have shared my C# application with others. When they open the application they get the error

  A network related or instance-specific error occured while establishing a connection to SQL Server. the server was not found or was not accessible

But the application is working fine on my PC. The connection string I am using is

Data Source=ASHISHPC1\SQLEXPRESS;Initial Catalog=ACW;User ID=ash159;Password=ashish159

which is stored in a .config file.

The application is working fine on my PC. What must I do? I have enabled the TCP/IP in the server but the same error persists. Some change in connection string or something else?


texts:
Getting started on creating data cubes with SSAS. I understand the various types of OLAP options ROLAP, HOLAP and MOLAP. However, when creating a data cube using SSAS (at least for 2008 version) I do not see where we choose this option. Would it be during the install of the server?

Where is the data stored when a data cube is created?


texts:
Have ISQL Values in Shell script as :
${ISQL} -U {DB_SERVER} -P {PASSWORD} >${tmp_file}

How can I pass Null values in either DB_SERVER or Password?
I tried below but it doesn't work (I'm using POSIX Shell script(Solaris))
${ISQL} -U{DB_SERVER} -P{PASSWORD} | sed -e -s/NULL//g' >${tmp_file}

but it doesn't seem to work.


texts: How to create root user after running mysql_install_db?

texts:
I'm installing MySQL on ubuntu and I changed the data directory. I created new data files using mysql_install_db command.

So now I need to access my database using root but it won't let me in. I tried starting the database by skipping the grant tables:

mysqld --skip-grant-tables --skip-networking &

Now I'm in, but the mysql.user table doesn't have a single row, which is logical after creating the data files from scratch, or isn't it ??

So I tried:

CREATE USER 'root'@'localhost' IDENTIFIED BY 'mypass';

But it won't run because I'm running with --skip-grant-tables !

So how can I create a root user from that point ?
And how can I stop the started mysql instance I just started with --skip-grant-tables because it seems I always have to killall mysqld to do it !


texts: How to select different format of the date in mysql

texts:
I have this table and I can't change its format to yyyy-mm-dd because I have so many scripts that are related to this format dd-mm-yyyy. It would be a mess if I change its format.

Id            date

1         01-07-2014
2         02-07-2014
3         03-07-2014
4         05-07-2014
5         07-07-2014
6         14-07-2014
7         18-07-2014
8         19-07-2014
9         21-07-2014
10        01-08-2014
11        02-08-2014
12        03-08-2014

On the php file

$from = '01-07-2014';
$to = '02-08-2014';

I need to update some values from all the dates that are between 01-07-2014 and 01-09-2014 to the format dd-mm-yyyy.

I am using

UPDATE successlog
   SET successlog.overtime ='00:00'
WHERE  date >= '$from'
       AND date <= '$to'

It is not working. I tried using the key between $from and $to. This does not work either.

When the format was yyyy-mm-dd it was working normally, but after I changed the format to dd-mm-yyyy, it is not working.


texts: Is it worth adding or rebuilding an index on a table already involved in an active query

texts:
I've kicked off an SQL Server query that has been going for 12 hours. Annoyingly I forgot to rebuild my indexes on one of the tables involved, I needed to do this as they are over 95% fragmented due to a database shrink.
So the position I'm in is basically waiting it out and stewing on what I forgot to do. I'm not going to kill the query as then I will enter the even longer and more frustrating roll-back hell.
But this has got me thinking...is there a way to add an index on a table already actively running a query and would the late arrival of healthy indexes speed up the remainder of that query?
After thinking about it my feeling is that just rebuilding an index on a table already involved in a running query would probably be very slow and slow down the query itself resulting in an even longer wait, but, is there a way to pause a query to carry out the addition of an index?
I suspect not as suspending one query mid-way to allow other things to happen feels like it could lead to mayhem, but I'm just interested to know if anyone has had any experience of this?
------------------- EDIT --------------------------------
Thanks for your responses.
This is the offending query complete with its plan:
https://www.brentozar.com/PasteThePlan/?id=SyP_Cu3Hu
Here's the query that ran much quicker without the clustered index:
quicker query


texts: SQL Server Always On does not send requests to read only replica

texts:
I have created an Availability Group with one primary and one secondary read only replica. The configuration is as below:

Now I connect to the listener using SSMS with parameter APPLICATIONINTENT=READONLY; but all read and write requests are sent to primary server as is shown in Profiler:

Is there any configuration mistake of me?


texts: SQL Server Basic Availability Group: config-only replica?

texts:
In SQL Server Basic Availability Groups docs, I found this interesting line:

Basic Availability Groups for SQL Server 2017 on Linux support an additional configuration only replica.

What is a configuration only replica?
Why is it only supported on Linux?


texts:
sqlplus can be used even if there is no listener in the system, or if it isn't running.

How does SQL*Plus connect to Oracle without a listener?

I can connect any user using SQL*plus even with no listener running.


texts: selecting from sys.dm_db_missing_index_details will start to return rows, and then sits and spins

texts:
I am trying to run some analysis on missing indexes on a server (I haven't ever done it on this server before).  I was running sp_blitzindex and it would hang up on "Inserting data into #MissingIndexes."  I dug into the query and found where it hits sys.dm_db_missing_index_details.

Trying to select from sys.dm_db_missing_index_details will return about 300 records, and then it'll sit and spin.  I didn't want to let it keep going to see how long it would take to finish, but I let it run for over two minutes and it didn't complete.

Why would this take a long time to return?  What would cause this?


texts: Performance problem after migration from SQL Server 2005 to 2012

texts:
Last Friday, I migrated a SQL Server instance from 2005 Enterprise Edition to a brand new Windows Server 2012 server with SQL Server 2012 Enterprise Edition installed. Since then, users have started to complain about the application performance.

Here is some information about the server:

Virtual server on VMWare 5.5
4 vCPU and 24 Gb RAM. On the previous configuration, 10 Gb was necessary but the tempdb database was smaller than what I set (almost 6Gb)
I now have set the maximum memory target to 22 Gb and tempdb is totally in Buffer Pool
After migration (performed using Database Mirroring), I ran update statistics, index rebuild, and other maintenance commands

I don't know where to find answers. If everything except the SQL Server instance is the same (VM, Disks, etc) then, to me, the problem is in SQL Server configuration, but where? I have tried some changes but none of them seem to have been useful.

Do you have any ideas?

Additional information from comments

I ran sp_Blitz which gave me some information, but nothing new from SQL 2005 (slow storage for instance)
The memory consumption (20Gb + ) is mostly due to tempdb (6Gb) and the remainings are mostly taken by an application database
PLE flows and sometimes goes down to 150. At the beginning of the day, most of the data is out of the cache
The max memory setting was 10Gb on the previous server
We use Idera diagnostic manager
The max server memory is set to twice the value it was on the previous server.
The page life expectancy drops below 150 at least twice a day

sp_BlitzIndex analysis

I started an analysis with sp_BlitzIndex and in addition to poorly written code, it showed this:

Aggressive Indexes: Total lock wait time > 5 minutes (row + page)

dbo.TABLE1.PK_TABLE1 (1):
Row lock waits: 3,591; total duration: 10 minutes; avg duration: 0 seconds;
Page lock waits: 23; total duration: 15 seconds; avg duration: 0 seconds;
Lock escalation attempts: 510,489; Actual Escalations: 1.

dbo.TABLE1.I_TABLE1_CNTTYPE_CATEGORY_IS_CURRENT (85):
Row lock waits: 155; total duration: 48 seconds; avg duration: 0 seconds;
Page lock waits: 129; total duration: 8 minutes; avg duration: 4 seconds;
Lock escalation attempts: 29,423; Actual Escalations: 4,951.

Would just an additional index creation do some changes in that phenomenon?

sp_configure

I've compared the output of sp_configure as requested. Here are the differences:

Config                          Old     New
Blocked process threshold       0       120
Maximum Degree of parallelism   0       4
Maximum Memory (MB)             10240   22000

The power option is already at High Performance. I set the memory back to 10 Gb with this command:

CHECKPOINT ;
DBCC DROPCLEANBUFFERS ;
EXEC sys.sp_configure N'max server memory (MB)', N'10240'
GO
RECONFIGURE WITH OVERRIDE
GO

After one hour of running with 10Gb of RAM: The last difference is the size of tempdb which is bigger than on the old server and uses now most of the memory, causing Page Life Expectancy to be hardly at 490.

Analysis of the Diagnostic Manager CPU statistics Report

The CPU statistics reports :

an average SQL compilation of 500
an average SQL Recompilation of 120
Up to 10 lock waits per minute, 5 on average
And mostly, a Table Lock Escalation which is on average of 40.

I've already set the Optimize for adhoc workload server setting and even "forced parametrization" for the most used user database.

So far, nobody told me there have been performance improvements. As it's a database I got back and was not managed by the DBA team, we don't have the background to check if it came back to a normal situation or not...

I will wait for some time and see whether it's ok for now or not.
Thank you all for your help !


texts: Get the name of the table column referenced by a (auto updatable) view column (PostgreSQL)

texts:
Given the folowing view definition

create or replace view projects as
select id, client_id as clientId from data.projects

Is it possible, knowing the view name and the view column to get the name of the original table and column names?

I know about information_schema.view_column_usage but it does not provide the column to column relation.
My thinking is that although it might not be possible to find out using a query, the information is available somehow since the column clientId is autoupdatable and PG needs to somehow translate it to an insert on the original table.

Is there a public function similar to pg_column_is_updatable that would return the oid of the referenced column?

EDIT:
The best i could come up with is this https://gist.github.com/ruslantalpa/2eab8c930a65e8043d8f


texts:
Consider the following table,

CREATE TABLE GapsIslands (ID INT NOT NULL, SeqNo INT NOT NULL);

ALTER TABLE GapsIslands ADD CONSTRAINT pk_GapsIslands PRIMARY KEY (ID, SeqNo);

INSERT INTO GapsIslands
SELECT 1, 1 UNION ALL SELECT 1, 2 UNION ALL SELECT 1, 5 UNION ALL SELECT 1, 6
UNION ALL SELECT 1, 8 UNION ALL SELECT 1, 9 UNION ALL SELECT 1, 10 UNION ALL SELECT 1, 12
UNION ALL SELECT 1, 20 UNION ALL SELECT 1, 21 UNION ALL SELECT 1, 25 UNION ALL SELECT 1, 26;

I am struggling to understand application of the NOT EXISTS condition in the context of a Gaps and Islands solution.

The following query is meant to find the starting points of 'islands':

SELECT ID, SeqNo, ROW_NUMBER() OVER (ORDER BY SeqNo) AS RowNum
FROM GapsIslands AS a
WHERE NOT EXISTS (
    SELECT  *
    FROM GapsIslands AS b
    WHERE b.ID = a.ID AND b.SeqNo = a.SeqNo - 1)

Now the query within the NOT EXISTS condition,

SELECT  *
    FROM GapsIslands AS b
    WHERE b.ID = a.ID AND b.SeqNo = a.SeqNo - 1

returns, the column (let's call this column A) SeqNo as:

 SeqNo
  1
  5
  8
  9
  20
  25

The 'full' column (let's call this column B) for SeqNo is:

  SeqNo
  1
  2
  5
  6
  8
  9
  10
  12
  20
  21
  25
  26

Why then, when we take column B values WHERE NOT EXISTS in column A, do we find the column

 SeqNo
  1
  5
  8
  12
  20
  25

The value 1 in column B is also IN column A, so then why is the value 1 appearing in the column SeqNo of the query as a whole?


texts: Update all rows but with limited number at a time

texts:
First of all, sorry if this question has been answered before. I am new to Microsoft SQL and StackExchange and I wasn't able to find help regarding this thing I want to improve.

So I have one task to update all rows in a table with data from another row.
This table has currently 3,9 million rows and counting.
I want to update all rows in the table but only 200k-300k rows at a time.

My current script looks like this:

alter table TableA add blob_image_tmp varbinary ( max )
GO

update TableA
 set blob_image_tmp = cast ( blob_image as varbinary ( max ) )
 where columnA < 200000
GO

update TableA
 set blob_image_tmp = cast ( blob_image as varbinary ( max ) )
 where columnA > 199999 and columnA < 400000
GO

update TableA
 set blob_image_tmp = cast ( blob_image as varbinary ( max ) )
 where columnA > 399999 and columnA < 600000
GO

....and so on up to 3,9million.

Then I end it with the following code:

alter table TableA drop column blob_image
GO

exec sp_rename 'TableA.blob_image_tmp' , 'blob_image' , 'COLUMN'
GO

alter table TableA rebuild

Is there a simpler way to achieve the same result?
I have another table I need to do the same thing with but that one has more than 10 million rows so it feels annoying to split each part up like this.


texts: Disaster Recovery through cloning drives in sql server 2014 enterprise

texts:
I am a DBA noob and recently my work has considered to have a disaster recovery plan for their production DB lets say A (sql server 2014 enterprise). The production server A and Disaster recovery server B are in different geographical locations. We have just 10 databases in the production A and each DB size does not exceed 4GB. During the installation, I have changed the default locations to D, E and F respectively and the system databases and tempdb files are stored in D and E drive as well. However I left the root directory as default in C drive in both the servers A and B. I have followed the similar path configuration in Disaster Recovery (DR) server as well.

So my management plan is to make clone copies in Disaster Recovery (B) server of all LUNs that are replicated each hour from production server A. Once this copies ( D and E drive from A) are completed, they are planning to mount these in DR server.

So my question, is this appropriate way to follow Disaster Recovery ? Also, what issues I might face ? I am little worried whether databases will be up and running once this mounting is completed in DR server ? If not what steps should I consider ? Any suggestions highly appreciated.

  Data : D:\MSSQL\Data\
  Log: E:\MSSQL\Data\
  Backup: F:\MSSQL\Backup


texts: How to use stored procedure in execute sql task to find the error exception

texts:
I created a Data Flow task with a table in SQL Server. I want to add execute SQL task to Data Flow task to give me error message and details of the error in case the data flow task is not success. For this I want to create a query in stored procedure and use in the execute sql task as it is a repetitive job.
So kindly help me to create a query in general to find if there is error and its details and how to use in execute sql task.

Thank you!


texts: Truncate partitioned table with values from partitioned column?

texts:
I have partitioned a table on a column with varchar datatype.

The documentation on truncate table with partitioning uses the partitionids:

Truncate table parttable with (partitions (1,2,5))

I only have a list of the actual values from the partitioned column.

How can I get the partitionids from a list of values so I can use it in the truncate table statement?

Background:

I’m still very new to partitioning and need to validate my plan.

I have a fact table that is partitioned on a column with varchar datatype.

I have an etl process that loads data fully in the beginning of the month.

The data loads after that only contain a subset (the partitioned column) and needs to fully replace the existing data in the fact table.

My plan is:

Load the data first to a staging table.
Truncate the fact table on the partitioned values in the staging table.
Insert the data in the staging table to the fact table.

Am I doing this correctly?


texts:
Recently we have encountered seriously problem since we moved our mysql database from EC2 to RDS.

We do have high cpu usage before moved to rds, but our website has a good speed for our customers.  After set up instance in RDS, our speed dramatically down without any reasons.

We checked our metrics on RDS, then we found that we have almost 100 write count/sec in anytime of day, even we do not have too many customers at nighttime, but the write count/sec is still remain around 100 count/sec.

And we have checked our general_log and slow_log files, but we do not where the write operations from ?

Anyone experience similar problems ?

Our configuration of rds:

Mysql 5.6.23 db.r3.large

Our metrics snapshot:

Many Thanks


texts: SQL Server 2014 Failover cluster - Nodes on different patch levels

texts:
As part of trouble shooting a performance issue with a SQL Server cluster I found out that the nodes haven't been patched properly to the same level. It is a 2 node active / passive cluster. SQL Server performance was  much better on node with higher patch level.

The nodes have been patched to the same level now.

Just trying to understand how SQL Server and the would have  behaved in this sort of a scenario?
Would this patch level miss match have caused other issue with the cluster?


texts: Get rows that exist exactly once per day for a given period

texts:
I have a case where I want to extract the device ids (DID) that exist once and only once for each day in a certain period. I have tried different methods and partitions but I seem to only be able to get that data individually per day (where date = X), but I need a query with where date between X and Y.

Example data:

DID date
A   2019-01-01
A   2019-01-01
A   2019-01-02
A   2019-01-03
B   2019-01-01
B   2019-01-02
B   2019-01-03
C   2019-01-01
C   2019-01-02
C   2019-01-02
C   2019-01-03
D   2019-01-01
D   2019-01-02
D   2019-01-03

The query should return only B & D because those exists once for each day from 01 to 03.
I also wish to get the count, which would be 2 in this case


texts: Check if two databases have same structure

texts:
I have a Postgres database that was developed over time, adding tables and keys when needed.
Now I'm using a tool (Enterprise Architect) to document it. The tool allows to create automatically the SQL needed to re-create the database.
I want to know if the database that I create and the original one have the same structure (same tables, same primary keys, same foreign keys etc). I'm not interested on data contained in database, only in structure.
Is there a way to check if two different databases have the same structure?


texts: Importing MySQL database using Linux Terminal

texts:
I have an existing database, named dlti.sql

I want to transfer it to a Linux-based server. I tried using this code, however I get no response:

mysql -u root -p Desktop < dlti.sql

The .sql file is located at the desktop.

Can anyone help me with this?


texts:
Imagine I have a basket from an online shop, with id of item and the quantity.
I have a list of items of quantity and I would like to find a basket with the exact same items and quantity for each.

I have found solutions to find matching sets, but not with duplicates or quantity field.

The basket could contain multiple times the same item id, or I could also choose to have a quantity field, depending on what's better

thanks

two solutions I see for database format :

id_basket id_product quantity
1           1           2
1           2           1
2           1           2
2           2           1
3           1           1
3           2           1

or

id_basket id_product
1           1
1           1
1           2
2           1
2           1
2           2
3           1
3           2

basket 1 and 2 contain the same thing, but not the third, and I would like to be able to find a basket that contains a list of product using "raw" data, so the idea is not to find duplicates on the database, but find one that matches a static list (like find a basket that contain 2x item 1 and 1x item 2)


texts: Do MySQL optimizations hold for MariaDB also?

texts:
I am a developer who will be starting a new project soon in which database performance will be crucial. The current candidate for the backend is MariaDB. I have worked with MySQL and MariaDB before in smaller projects, but the field of bloody optimization is quite new to me, admittedly.

To prepare for the project and not drive it into the ground, I'd like to learn about database performance optimization first. I'd like to know if I (or my employer) bought a book about optimizing MySQL performance, would it apply to MariaDB similarly?

My intuition tells me, that most of the MySQL optimizations would apply to MariaDB too, for they change quite a huge portion of their code base, but to avoid wasting money by buying books which we don't need, I'd like to clarify this. Vice versa a book only applicable to MariaDB would be useless to us if we tried optimizing old products. Unfortunately - as far as I know - there are no books dedicated to MariaDB performance yet. Does this hold?


texts: Are SQL unit tests supposed to be so long?

texts:
I am writing stored procedures with some non-trivial business logic. I am trying to unit test them, but the actual tests end up being quite long (shorter ones starting at 40-50 LoCs, using commonly 4 different tables), which doesn't seem very "unit". (Admittedly, I format my code in a way where it takes a lot of space.)
In context of "normal" programming languages I've heard the advice to refactor the complex procedure into smaller chunks. But I don't want to do that here because:

I don't want to pollute  "global namespace" by small routines called from one place only.
Passing around tables from and to stored procedures is cumbersome.
Custom functions can have negative effects on performance.

Am I wrong about this reasoning?
I am new to unit testing, so perhaps I am just writing my tests wrong?
Is SQL longwinded language and thus it's unit tests are longer as well?

(I am using SQL Server with tSQLt framework, but I believe the question is system-agnostic.)


texts: Deadlock error isn't returning the deadlock SQL

texts:
Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

I am getting this error randomly when one of my websites gets busy. I know roughly which sets of tables it is happening on but in my experience with other programs I normally get the SQL returned where the deadlock is happening. Is there a flag I should turn on to allow this to happen?

I will try and debug the deadlock itself as a seperate issue as this is my main question for now.

I am using SQL Server 2008 Standard Edition.


texts: SQL Server Average Lock Wait time Historical data

texts:
Monitoring software has indicated average lock wait times rising to 500ms during the night.

Is there any way of confirming which locks these were as we currently do not log any performance data to a table so would be relying just on DMV's.

Will be looking at writing performance data to a table for future reference.


texts: How to recover deleted records with minimal downtime?

texts:
In an OLTP environment that cannot tolerate downtime, suppose if 100 records are deleted from a table, how do we recover them?

One way to recover the deleted records is to restore the database and/or tablespace then rollforward to point-in-time.


texts: SQL Agent PowerShell task never finishes

texts:
I'm quite new to PowerShell, and now I've found dbatools.io, where I would like to run a PowerShell task in SQL Agent like

$ExportPath = $env:TEMP + '\DriveSpace.csv'
$datatable = Import-Csv $ExportPath | Out-DbaDataTable
Write-DbaDataTable -SqlServer MyServer -Database Utils -InputObject  $datatable -Table dbo.FreeSpaceOnDiskDrive -AutoCreateTable

It starts, but it never finishes.
I've also tried to have a task that is doing this:

Get-DbaDatabaseSpace -SqlServer MyServer -IncludeSystemDBs | Out-DbaDataTable | Write-DbaDataTable -SqlServer MyServer -database utils -Table dbo.DiskSpaceExample -AutoCreateTable

Same problem. It runs ok in Windows PowerShell ISE, but just hangs in SQL Agent.
So now it has created a new table for me, and populated it with data, but the job just keep on running.


texts: Postgres use multiple tablespaces in a single database

texts: Is it possible to allocate tables directly to tablespaces within a single database/schema in postgres? Based on the docs, tablespace is a create database argument, but are there any workarounds?


texts:
I typically avoid cursor operations like the plaque, however, I have come across my first problem where using a cursor outperforms a query. So I am forced to use it.

I have created an elaborate reporting stored procedure that is used by many users. I use a cursor to iterate a list and insert data into a @TEMP_TABLE, finally selecting the temp table as result set.

The cursor is used as follows:

DECLARE HIGHLIGHTS_REPORT_CURSOR CURSOR LOCAL FOR
SELECT RowID, UserID,GradeID,ClassID,MenuSetID,CurrentSequence FROM @DATA
OPEN HIGHLIGHTS_REPORT_CURSOR
FETCH NEXT FROM HIGHLIGHTS_REPORT_CURSOR INTO @RowID,@UserID,@GradeID,@ClassID,@MenuSetID,@CurrentSequence
WHILE(@@FETCH_STATUS=0)BEGIN
    ...
    FETCH NEXT FROM HIGHLIGHTS_REPORT_CURSOR INTO @RowID, @UserID,@GradeID,@ClassID,@MenuSetID,@CurrentSequence
END
CLOSE HIGHLIGHTS_REPORT_CURSOR
DEALLOCATE HIGHLIGHTS_REPORT_CURSOR

So all is well, the report moved through QA and no problems until user based ramped up now I am getting exceptions logs sent to me sporadically with the following error:

Server Log Reference ID : b91a8f4a-b944-4355-bba3-2855fd126c2b
Message : A cursor with the name 'HIGHLIGHTS_REPORT_CURSOR' does not exist.
Source : HighlightsReport

Stack Trace :    at System.Data.SqlClient.SqlConnection.OnError(SqlExceptionexception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

I can state with 100% confidence there is not another cursor with the same name. There are no errant CLOSE\DEALLOCATE CURSOR calls between BEGIN and END.

I learned that SQL Server defaults the "default cursor" database property to GLOBAL during installation. Since the report procedures are the only SP's where cursors are used I have changed the "default cursor" to LOCAL.

That did not help:( Maybe "default cursor" is a setting the requires a restart. Instead of restarting I used the LOCAL attribute of the CURSOR declaration.

This ensures that my cursor is scoped to the SP but still getting the error above???

I am starting to think this is a concurrency issue with multiple connections hitting the SP at the same time. This would explain the sporadic behavior and why this was not caught during QA.

Would it be possible that two connections call the procedure at approximately the same time in which one connection hits the DEALLOCATE HIGHLIGHTS_REPORT_CURSOR just before the second connection hits the FETCH NEXT FROM HIGHLIGHTS_REPORT_CURSOR INTO block??

I assumed using LOCAL ensured each connection would have its copy but it seems to not be the case? Any ideas?

Updated. This is bizarre I got three exception messages stacked into one.
I wonder if a client connection calling the same sp in quick succession cause something similar.

Server Log Reference ID : b91a8f4a-b944-4355-bba3-2855fd126c2b
Message : A cursor with the name 'HIGHLIGHTS_REPORT_CURSOR' does not exist.
          A cursor with the name 'HIGHLIGHTS_REPORT_CURSOR' does not exist.
          A cursor with the name 'HIGHLIGHTS_REPORT_CURSOR' does not exist.
Source : HighlightsReport

Update 2 The part that makes me think this is a user concurrency issue is the fact that these errors always come in groups of two. I receive an exception report with the error noted above from two different users at the same time.


texts: Can we restore backup for standby from external hard drive with differnet drive labels

texts:
I am using windows server 2003 and Oracle database 10g R2 and i have taken the backup using RMAN of my primary database in Drive label E. The size of backup is 1.5TB. In my standby database i don't have much space to copy the backup of primary database in standby machine. My standby machine have only space for restore the backup. i have copied the backup files in my external hard drive label G. Now i want to restore my backup using external hard drive label G.
can i be able to restore it?
please share your experience and documentations.


texts:
Sometime ago I noticed my WordPress sites wont load. I found the problem with MySQL.

The server I am running on is a VPS server with:

Debian 8.1
MariaDB - Ver 15.1 Distrib 10.1.14-MariaDB
2048 MB RAM
45GB SSD
2 vCores

What I did was to add innodb_force_recovery=2 right after the [mysqld] section tag. I restarted mysql and it loaded in read only mode.

Then, I wanted to find which table is corrupt, so I ran mysqlcheck -u root -p --all-databases and then entered my password to login. The output started going table by table (line-by-line) showing OK at the end. Then it gets into the biggest database I have which is made of three tables (2.1 million rows, 45 million rows, and 1.9 million rows). It does not show any output at that stage, and it just hangs there.

Is there a better way to find out which table is corrupted? Can I print only the bad tables, instead of printing OK for everything?

UPDATE
/var/log/syslog has the following entries after mysql service restart:

[Note] /usr/sbin/mysqld: Normal shutdown
[Note] Event Scheduler: Purging the queue. 0 events
[Note] InnoDB: FTS optimize thread exiting.
[Note] InnoDB: Starting shutdown...
[Note] InnoDB: Shutdown completed; log sequence number 32047521550
[Note] /usr/sbin/mysqld: Shutdown complete
[Note] /usr/sbin/mysqld (mysqld 10.1.14-MariaDB-1~jessie) starting as process 29607 ...
[Note] Using unique option prefix 'myisam_recover' is error-prone and can break in the future. $
[Note] InnoDB: Using mutexes to ref count buffer pool pages
[Note] InnoDB: The InnoDB memory heap is disabled
[Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
[Note] InnoDB: Memory barrier is not used
[Note] InnoDB: Compressed tables use zlib 1.2.8
[Note] InnoDB: Using Linux native AIO
[Note] InnoDB: Using SSE crc32 instructions
[Note] InnoDB: Initializing buffer pool, size = 256.0M
[Note] InnoDB: Completed initialization of buffer pool
[Note] InnoDB: Highest supported file format is Barracuda.
[Note] InnoDB: 128 rollback segment(s) are active.
[Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.29-76.2 started; log sequence numbe$
[Note] InnoDB: !!! innodb_force_recovery is set to 2 !!!
[Note] InnoDB: Dumping buffer pool(s) not yet started
[Note] Plugin 'FEEDBACK' is disabled.
[Note] Server socket created on IP: '127.0.0.1'.
[Note] Reading of all Master_info entries succeded
[Note] Added new Master_info '' to hash table
[Note] /usr/sbin/mysqld: ready for connections.
Version: '10.1.14-MariaDB-1~jessie'  socket: '/run/mysqld/mysqld.sock'  port: 3306  mariadb.org binary distribution
May 23 16:38:38 warp debian-start[29635]: Checking for corrupt, not cleanly closed and upgrade needing tables.
mysqld[29607]: InnoDB: innodb_force_recovery is on: we do not allow
mysqld[29607]: InnoDB: database modifications by the user. Shut down
mysqld[29607]: InnoDB: mysqld and edit my.cnf so thatInnoDB: innodb_force_... is removed.


texts: Range X-X lock on table with primary key value

texts:
I have a table with a primary key and clustered index. I am updating the table as below,

update MyTable1
set
 SomeColumns = @SomeColums
where
  MyPkColumn=@MyPkColumn

I have another table with the same design where I delete records,

delete from MyTable2
where
  MyPkColumn=@MyPkColumn

Now in my logs sometimes I see deadlocks as below,

1) Process1 has RangeX-X lock on MyTable1 and waiting for S-U lock on MyTable2.
2) Process2 has X lock on MyTable2 and waiting for RangeX-X lock on MyTable1.

Now, MyTable1.MyPkColumn is the primary key why there is RangeX-X lock?

Update: The dataType of the parameter is different from MyPkColumn(specifically VARCHAR and NVARCHAR).

Update: We are using default isolation level READ COMMITTED.


texts: Reformatting master MySQL server and temporary solution

texts:
We have MySQL master and replication servers both running on different machines. We have noticed some system files showing integrity issues in the master machine. Temporarily we would like to make the slave as a master and reformat the original master and then bring back everything to the original architecture.

What are the crucial steps now for us to maintain data integrity?

We can down the master and let the inserts and update take place in the replication db, but how do we revert back to the original situation later?


texts: PostgreSQL - triggers to fire only on replicated db

texts:
Is it possible to fire triggers only on a read-only Postgres replicated database?

I want triggers to fire only on the replicated database - not on the master (to avoid load). This is to track - say - all updates/deletes.

Would enabling replica trigger be the way to go?
On the primary/master, the triggers remain disabled, but in the replicated db, we can have the triggers fired?

The wal2json plugin is an option, but triggers seem a safer bet.


texts: Why is using a table variable more than twice as fast as a #temp table in this specific case?

texts:
I was looking at the article here
Temporary Tables vs. Table Variables and Their Effect on SQL Server Performance and on SQL Server 2008 was able to reproduce similar results to those shown there for 2005.

When executing the stored procedures (definitions below) with only 10 rows the table variable version out performs the temporary table version by more than two times.

I cleared the procedure cache and ran both stored procedures 10,000 times then repeated the process for another 4 runs. Results below (time in ms per batch)

T2_Time     V2_Time
----------- -----------
8578        2718
6641        2781
6469        2813
6766        2797
6156        2719

My question is: What is the reason for the better performance of the table variable version?

I've done some investigation. e.g. Looking at the performance counters with

SELECT cntr_value
from sys.dm_os_performance_counters
where counter_name = 'Temp Tables Creation Rate';

confirms that in both cases the temporary objects are being cached after the first run as expected rather than created from scratch again for every invocation.

Similarly tracing the Auto Stats, SP:Recompile, SQL:StmtRecompileevents in Profiler (screenshot below) shows that these events only occur once (on the first invocation of the #temp table stored procedure) and the other 9,999 executions do not raise any of these events. (The table variable version does not get any of these events)

The slightly greater overhead of the first run of the stored procedure can in no way account for the big overall difference however as it still only takes a few ms to clear the procedure cache and run both procedures once so I don't believe either statistics or recompiles can be the cause.

Create Required Database Objects

CREATE DATABASE TESTDB_18Feb2012;

GO

USE TESTDB_18Feb2012;

CREATE TABLE NUM
  (
     n INT PRIMARY KEY,
     s VARCHAR(128)
  );

WITH NUMS(N)
     AS (SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY $/0)
         FROM   master..spt_values v1,
                master..spt_values v2)
INSERT INTO NUM
SELECT N,
       'Value: ' + CONVERT(VARCHAR, N)
FROM   NUMS

GO

CREATE PROCEDURE [dbo].[T2] @total INT
AS
  CREATE TABLE #T
    (
       n INT PRIMARY KEY,
       s VARCHAR(128)
    )

  INSERT INTO #T
  SELECT n,
         s
  FROM   NUM
  WHERE  n%100 > 0
         AND n <= @total

  DECLARE @res VARCHAR(128)

  SELECT @res = MAX(s)
  FROM   NUM
  WHERE  n <= @total
         AND NOT EXISTS(SELECT *
                        FROM   #T
                        WHERE  #T.n = NUM.n)
GO

CREATE PROCEDURE [dbo].[V2] @total INT
AS
  DECLARE @V TABLE (
    n INT PRIMARY KEY,
    s VARCHAR(128))

  INSERT INTO @V
  SELECT n,
         s
  FROM   NUM
  WHERE  n%100 > 0
         AND n <= @total

  DECLARE @res VARCHAR(128)

  SELECT @res = MAX(s)
  FROM   NUM
  WHERE  n <= @total
         AND NOT EXISTS(SELECT *
                        FROM   @V V
                        WHERE  V.n = NUM.n)

GO

Test Script

SET NOCOUNT ON;

DECLARE @T1 DATETIME2,
        @T2 DATETIME2,
        @T3 DATETIME2,
        @Counter INT = 0

SET @T1 = SYSDATETIME()

WHILE ( @Counter < 10000)
BEGIN
EXEC dbo.T2 10
SET @Counter += 1
END

SET @T2 = SYSDATETIME()
SET @Counter = 0

WHILE ( @Counter < 10000)
BEGIN
EXEC dbo.V2 10
SET @Counter += 1
END

SET @T3 = SYSDATETIME()

SELECT DATEDIFF(MILLISECOND,@T1,@T2) AS T2_Time,
       DATEDIFF(MILLISECOND,@T2,@T3) AS V2_Time


texts:
I have two PostgreSQL tables that each contain a column called date_of_incident.

They each also have a column called incident_id and this is how the tables are related.

I want to update all rows of table 1 by setting date_of_incident equal to date_of_incident from the corresponding row of table 2 where incident_id from table 1 equals incident_id from table 2.

Can anyone can give a sample UPDATE? I'd appreciate it.

incident_id is unique in both tables and it is how the tables are related, though not by foreign key or constraint (sadly).  I have the extra condition of status = closed.  The only complication is that table 1 has a trigger function on update and I need to examine to see how the SQL will affect things.


texts: SSAS : how to disable grouping in "Age" property of a fact?

texts:
In SSAS, I have:

the dimension ANESTHESIT, which has the key ID
the fact PATIENT, which key is ID and has a field PAT_ANESTHESIST that is used in a relationship between ANESTHESIT and PATIENT(the relationship is defined in the data source view and appears also in the cube)
and some datas inside.

The problem appears when trying to browse the cube. I add some columns : the name of the anesthesist, and the age of the patient. And I get a table with 2 columns : The anesthesist names, and for each anesthesist, the sum of the patients' ages related to this anesthesist. That's not what is expected, namely one column for each patient.
I saw I could change in the cube structure the AggregateFunction of the "Age" attribute, from Sum to None  but I only have the standard version of SQL Server, so I guess I have only the standard version of SSAS, but the Enterprise version of SSAS seems to be required to enable "None" in AggregateFunction.
Can you tell me if there is no other way to achieve this rather basic functionality?
thank you.


texts: How can I enforce ensure uniqueness of an association with a time dimension?

texts:
I have a database table AB which tracks the association of A and B over time:

create table AB columns (
   ARef ...,  -- FK of A
   BRef ...,  -- FK of B
   Since as Timestamp,
   Until as Timestamp,
   SomeAttrib1 ...,
   SomeAttrib2 ...
);

I would like to declare a unique key such that the same A and B can only be associated together once during the same interval.

What strategies might I use for expressing such a constraint?  Clearly, just doing a PK on ARef,BRef,Since,Until will not suffice - that would only prevent duplicates of the exact same interval.  I need to consider how a row's interval overlaps or does not overlaps with the intervals of other rows in the table.

Parameters to consider:

I am using SQL Server 2012, but I'm also interested to know what facilities are available in other RDBMS's.
Although I realise that keeping this 'history' or 'time dimension' could be considered an 'analytical' use of a database, I'm not wanting a solution that uses a 'dimension' or 'cube' ... this is an ordinary transactional database for an application which needs to assign different attributes to associations which occur at different times.
I'm not heavily committed to the timestamps ... happy to consider 'interval' datatypes if they would help.


texts: How does paging work with ROW_NUMBER in SQL Server?

texts:
I have an Employee table that has one million records. I have following SQL for paging data in a web application. It is working fine. However what I see as an issue is - the derived table tblEmployee selects all records in the Employee table (to create the  MyRowNumber values).

I think, this causes selection of all records in the Employee table.

Does it really work so? Or is SQL Server optimized to select only the 5 records from the original Employee table too?

DECLARE @Index INT;
DECLARE @PageSize INT;

SET @Index = 3;
SET @PageSize = 5;

SELECT *  FROM
  (SELECT  ROW_NUMBER() OVER (ORDER BY EmpID asc) as MyRowNumber,*
  FROM Employee) tblEmployee
WHERE MyRowNumber BETWEEN ( ((@Index - 1) * @PageSize )+ 1) AND @Index*@PageSize


texts: How can I tell when InnoDB recovery is complete/check its progress?

texts:
I'm writing a process to bring up a new instance from our nightly database snapshots, and I want to watch its progress and perform some actions once the database is up. When mysql first comes up, it spends some time in recovery, and I'd like to know when this is complete, and preferably how far along it is.

Linux "service" claims mysql is in "start/running" state, so that's no help. I can just try to connect until a connection is successful, but then I could be hung forever if something goes wrong during recovery. The closest I've been able to get so far is to tail MySQL's error.log, then use a regex to pull the last number off the last line to determine recovery progress. But that seems a bit fragile and hacky.

Is there any sort of tool MySQL (or third party) provides to know for sure that MySQL is currently in recovery and how far into the process it is?


texts: Package performs differently on two different instances on the same server

texts:
I'm investigating some performance issues for our BI team.  They have a package which runs extremely slow and will sometimes run indefinitely on one instance but finishes normally on another instance.  Both instances reside on the same server and both are using the same package.  I've tried collecting PerfMon counters which show Page Life Expectency well bellow 300 and eventually reaching 0.

I'm also seeing ASYNC_NETWORK_IO wait types when I use Activity Monitor to see what is occurring.  The ASYNC_NETWORK_IO occurs when it tries to execute a stored proc which I've scripted out and am able to run to completion in under 30 seconds.

I'm not quite sure where I should look for what is causing the Page Life Expectency to be so low or if I should be tracking down the ASYNC_NETWORK_IO waits.

These instances are running SQL Server 2008 R2 on a W2K8 R2 VM with 32 GB of ram.

What is the ideal troubleshooting path for two instances on the same server performing differently?


texts: Is it possible to make a reference to the result of an aggregate function in a SELECT clause from the same SELECT clause?

texts:
I'm not a Database Administrator--just a Software Engineer. I would like to know if it is possible to reduce the following T-SQL query:

SELECT
  SUM(Price * Quantity) as 'Total',
  SUM(Price * Quantity) * 0.95,
FROM
  SomeTables;

To something like this:

SELECT
  SUM(Price * Quantity) as 'Total',
  'Total' * 0.95,
FROM
  SomeTables;

Note: This is just a sample to clarify/support my question. My current SQL query is larger and more complex than this.