texts: When exploring the features of Oracle Active Dataguard, I found that one of them is block change tracking to allow the standby db to take the load of backups instead of the primary database doing them. Is there a need for taking backups of the primary DB in the first place, when the standby database can be used for recovery directly?


texts: Why do I get this error "Statement failed, SQLSTATE = 08001"?

texts:
When ever I try to connect with Firebird SQL I get this error,

firebird$ isql-fb 'db.FDB' -u SYSDBA -p <PASS>

Statement failed, SQLSTATE = 08001
I/O error during "open" operation for file "CAFW.FDB"
-Error while trying to open file
-Permission denied
Use CONNECT or CREATE DATABASE to specify a database

What can I do to resolve it, I've already ensured that my current user has read/write access to the file.

$ sudo chown firebird:firebird ./db.FDB
$ sudo chmod 770 ./db.FDB # should only need 660

$ ls -lah
-rw-rw---- 1 firebird firebird 53M May  2 17:13 CAFW.FDB


texts: noexpand hint for non-enterpise edition and performance

texts:
I have to use Indexed views to reach performance. As I can see from this comparison table standard edition does not support indexed views. But BOL says:

  Indexed views can be created in any
  edition of SQL Server. In SQL Server
  Enterprise, the query optimizer
  automatically considers the indexed
  view. To use an indexed view in all
  other editions, the NOEXPAND table
  hint must be used.

So will it work (I am talking about performance)

select * from dbo.OrderTotals with (noexpand, index=IXCU_OrderTotals)

on SQL Server Standard edition
as well as it works

select * from dbo.OrderTotals

on the Enterprise one?

Here is code for view:

CREATE VIEW dbo.OrderTotals
WITH SCHEMABINDING
AS
select
    OrderId     =   r.OrderId
  , TotalQty            =   SUM(r.Quantity)
  , TotalGrossConsid    =   SUM(r.Price * r.Quantity)
  , XCount      =   COUNT_BIG(*)
from dbo.Order r
group by r.OrderId

CREATE UNIQUE CLUSTERED INDEX IXCU_OrderTotals ON OrderTotals (OrderId)


texts: Social (Classmate like) database schema appreciation

texts:
This is a database schema I propose for a networking website like the Classmates.com web site.

Do you think the database has all the tables needed for such a project? What would you rather do?

The database has 3 parts:

the payment part is surrounded by the black color box
the social part by the blue color box
the classmates part by the red color box

Any advice or critics is welcome. thanks.

I have been inspired by database models presented on the databaseanswers.org web site.


texts:
Is it possible from SQL Management Studio to create a backup directly to an FTP server?
Basically, instead of choosing the destination to a disk, I sould like to be able to specify an FTP location (server, user, pass) to send the backup file to.

This prevents the backup data from consuming space on the server itself.
Network transfer through a shared folder is not an option for security reasons.


texts: Does Postgresql create a new tuple for an no-change update statement?

texts:
I need to fix some data issues in a Postgresql database, the primary of which is trimming whitespace from text columns. I'm doing this using a statement like:
UPDATE app.products
SET "description" = TRIM(BOTH FROM "description")

Issuing this on a test database returns UPDATE 2000. The test table contains 2000 rows of which only 1 actually would change with whitespace trimming. Is this resulting in Postgres creating new, unnecessary tuples? Would there be a benefit to adding a WHERE clause? Most of the target columns aren't indexed.
UPDATE app.products
SET "description" = TRIM(BOTH FROM "description")
WHERE "description" LIKE ' %' OR "description" LIKE '% '


texts: execute() vs executeQuery() in MySQL Connector C++

texts:
With MySQL Connector C++, I can use the sql::Statement::execute() function if I am going to modify the database (INSERT, UPDATE, DELETE, MERGE), or the sql::Statement::executeQuery() function if I am going to do a simple read only query.

Supposing I do not know what is in the query, how can I choose what to do?

At the moment I just check if in the query string there are any of the INSERT, UPDATE, DELETE or MERGE tokens. But that is a far from perfect way to do it.

Is there another method to do it?

Should I just ignore the warning messages returned by MySQL when I use executeQuery() to modify the DB?

Thank you.


texts:
I have to achieve 3NF with following specifications:
Database where I will determine priority skills for specific jobs and track the employees ability to complete them. Train employees.

Example of job position and employee:
JP Constructor / needed skill AUTOCAD - excellent
Name Surname / JP constructor / skill AUTOCAD - not trained

Catalog of skills(group, code, title), groups of skills, for example:
-general skills (foreign languages, administrative skills,...)
-specialized software (AutoCAD, CADSS,...)

ID in catalog of skills is generated according to model:

Specialized skills..   X(group of skill)
RBR                    1001
ID                     X.1001

For job position skill and employee, there are 3 levels:
-familiar
-intermmediate
-excellent

Track skills for job position in department.

Output documents:
-overview of employees with specific skill
-comparing of skills for job position in department and skills of employees on that job position, to get the plan of acquiring knowledge for specific skills (list the employees who have skills with LEVEL not sufficient enough that job position requires)

I wrote detailed info above so you get the concept idea.

This is done in Oracle. I have .csv file with rows and data which I'll import but they're not in 3NF.
Now my attempt to apply this to tables/ER model:

I already have DEPARTMENT table pre-generated, so thats done. I thought about creating 3 tables to achieve model:

Primary key will be marked inside [], and foreign with ()

EMPLOYEES([ID], Name, Last_Name)
JOB_POSITION([ID], (EMP_ID), JP_Title, Group, [Department])
SKILLS([ID], (EMP_ID), SK_Title, Level)

Now, this is what I got as suggestion on my first model. And another, that I put the rest as materialized views. But somehow I don't know if this is the right way to do it.

In short, I'll have to track employees on certain job positions with certain skills(if they are sufficient or not).

I get kind of blurry on realization.

I'd really appreciate some direction here, thank you.


texts: How to create user that can access to "master" database

texts:
Recently I have created a TABLE that is under Database > System Databases > master > Tables > my_table.
(refer to the image below)

Now I would like to create a user that can access this Table, but don't know how it works. (I wanted to use C# to connect to this Table)


texts: Get Schedule for the current day or first coming up

texts:
I am building a scheduling app, where I assign schedules to certain members but those schedules can change at a certain date or be assigned for the future.

My database structure is like so:

members table:
------------------------------------
member_id

schedule table:
------------------------------------
schedule_id
schedule_name

assigned_schedules asch table:
------------------------------------
member_id
schedule_id
effective_from
effective_to

The effective_to and effective_from values are a unix timestamp. I currently have joined the tables but my where clause is quite redundant and has errors in it. I am looking to get the schedule currently assigned for the current day of a member, or the closest assigned schedule if there is one assigned in the future. A member can have a schedule assigned to a future date and it would change at that time, but I am looking for a daily view.

Currently, I join all the tables and have this where clause (where the ? is today's timestamp):

( asch.effective_from  <= ? AND ( asch.effective_to IS NULL OR asch.effective_to >= ? ) OR asch.effective_from IS NULL )
XOR
( ( asch.effective_from > ? AND asch.effective_to IS NULL ) XOR asch.effective_from > ?  )

assigned_to value can be null, which means that the schedule has no termination time.


texts: Calculating storage requirements of individual fields based on actual usage and properties?

texts:
I am attempting to determine if anyone has built a handy utility to query a set of fields and estimate a range of expected data usage of the set of fields.

I have looked at the Microsoft Article describing the process, as well as saw this other similar question but it just describes a generic process.

What I am looking for is query to tell me the estimated actual field usage requirements (minimum, medium, maximum) of a particular fields based on how they are actually utilized (i.e. is only 5 bytes of a 12 byte varchar in use) and taking into consideration the data type vs. another set of fields.

That is, if I have Fields A+B+E+G+Z2 it will requires between 9232 and 12344 space for 1 million rows, but if I do A+B+E+G it will only require 7212 to 9632 space for 1 million rows based on the actual usage of the existing fields.

The reason I am asking is I am trying to easily determine data storage requirements and retention for existing fields and weighing them against their usefulness for business purposes. Yes you can argue storage is cheap but I am looking for a utility query to perform exactly this.

At this point I do not care so much about the index size. In SQL 2008 I cannot find a utility to do this, and the closest thing I can get to it is the Top Tables Report for the entire table and all fields.


texts: Migrating master databases between clusters by detaching/attaching them

texts:
As a migration strategy is it possible or feasible to detach a master database in a Microsoft SQL Server cluster and then attaching a different master database's files from another cluster?

The goal is to migrate a cluster from one server to a different one.

Both are SQL Server 2000.


texts: Oracle Procedure to Enable/Disable all constraints

texts:
I have this nice piece of code:

begin
for i in
(
    select constraint_name, table_name
    from user_constraints
    where constraint_type ='R'
    and status = 'ENABLED'
) LOOP
execute immediate 'alter table '||i.table_name||' disable constraint '||i.constraint_name||'';
end loop;
end;
/

I'm using if very often for my migration so it would be even better to create a procedure out of if. The procedure should accept one boolean argument (should it enable or disable constraints), but frankly I had never wrote a procedure before. Any help would be appreciated.


texts: Error when installing Oracle 11.2.0.3 in Windows Server 2008 R2 64 bits

texts:
I downloaded Oracle 11.2.0.3 installer:

p10404530_112030_MSWIN-x86-64_1of7.zip
p10404530_112030_MSWIN-x86-64_2of7.zip
p10404530_112030_MSWIN-x86-64_3of7.zip
p10404530_112030_MSWIN-x86-64_4of7.zip
p10404530_112030_MSWIN-x86-64_5of7.zip
p10404530_112030_MSWIN-x86-64_6of7.zip
p10404530_112030_MSWIN-x86-64_7of7.zip

Then I unzipped them in same server on path:
C:\Software\Oracle 11.2.0.3 64 bits\oracle\

As an administrator user and without UAC in Windows, when trying to install Oracle software (not database) I get error which is attached in image file.

Previously I installed Oracle 11.2.0.1 software which I uninstalled before.

Does anyone got a similar error once? What to do in order to install Oracle 11.2.0.3 software?


texts: SQL db query: how to add a new field and assign values

texts:
I have 3 tables as shown in the screenshot below. I have created a query using UNION as shown below.

SELECT T1_ID AS ID, T1_NAME AS NAME, T1_ADD AS ADDRESS
FROM Table1
UNION
SELECT T2_ID AS ID, T2_NAME AS NAME, T2_ADD AS ADDRESS
FROM Table2
UNION
SELECT T3_ID AS ID, T3_NAME AS NAME, T3_ADD AS ADDRESS
FROM Table3

And I got the result below.

My question is, how do I add a column (field) called TYPE and assign values to the query results but not updating the existing three tables to make it like the screenshot below?


texts: Convert MySQL table date/datetime column to int (unix_timestamp) with existing records

texts:
I have tables with existing data, that use date?  and datetime` fields, that I want to convert to unix timestamp values (ints).
For some of the tables, creating a view that calls unix_timestamp() on the column, and saving the complete result set to a SQL file with MySQL workbench, and executing the SQL inserts into a new table is acceptable.
For other tables, there are way too many records for this approach.
Questions

Is there a way to transform a field using mysqldump?
Or to transform the fields while importing a mysqldump file?
Or to transform the data type of all the table record fields in place?


texts: How do I pass a variable into -job on Copy-DbaAgentJob?

texts:
I'm trying to query one the source instance and copy across any missing jobs into the destination instance. Everything seems to work except passing the name into -job. Can anyone suggest a fix or point me in the right direction?

$dest = "Dest\Instance"
$Source = "Source\Instance"
$MissingJob = Invoke-DbaQuery -SqlInstance $Source -ReadOnly -Query "
use msdb

select name
from dbo.sysjobs
where name COLLATE Latin1_General_CI_AS not in (select name
from [Dest\Instance].[msdb].[dbo].[sysjobs]
)"

foreach ($Name in $MissingJob){
Copy-DbaAgentJob -Source NCCSQLDEVW2K162\NCCSQL2017DBA01 -Destination $dest
-Job $Name -SourceSqlCredential $cred -Force
}


texts: MySQL Workbench: "Operation has completed with warnings"

texts:
As I synchronize my database, MySQL Workbench reports:

  "Operation has completed with warning. Please see logs for details."

But the Message Log just says:

Fetching schema list.
OK
WARNING: Server configuration check

I don't know what this is supposed to mean. Is the Workbench warning me about some dubious relations I implemented in my database or is something wrong with my server? What does it want from me?

Btw: The generated script runs without errors. The database gets generated and migrated to the server.


texts: schema for mysql database with 30million rows

texts:
We am building a SaaS backend for restaurants. We integrate directly with POS, so each POS keeps sending customer orders that we store in a following table in mysql.

CREATE TABLE name (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `amount` float DEFAULT NULL,
  `cc_last4` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `employee_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `menu_items` varchar(10240) COLLATE utf8_unicode_ci DEFAULT NULL,
  `payable` float DEFAULT NULL,
  `pos_type` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `pos_version` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `barcode` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `receipt_datetime` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `subtotal_amount` float DEFAULT NULL,
  `token` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `transaction_no` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `business_id` int(11) NOT NULL,
  `employee_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `location_id` int(11) NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `created_at_location_id_barcode` (created_at, location_id, barcode(25)),
KEY `index_receipts_on_barcode_and_location_id` (`barcode`,`location_id`),
KEY `index_receipts_on_barcode_and_business_id` (`barcode`,`business_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Few points of above schema:

Except Primary Key(id), none of the fields are unique. so created a unique index on date, location & barcode to avoid duplicates incase the POS machine sends same data twice.
menu_items column is supposed to contain individual items that customer ordered, which are normally below 255chars in 99.9% of the cases. but for remaining 0.1% cases(say its a catering order) the size of menu items increases & there is no limit on how large it can get. But we chose to truncate those to 10240chars. Not ideal, what's better alternative?
Currently, this table has 30 million rows(6months of data) & it is expected to get doubled every 6months. Out of which we only use past 3-4months of data on active basis.
No Partitioning or sharing has been done(yet)
Data once inserted, is seldom updated. its only read. so our queries are mostly INSERT & SELECTS. No UPDATE statements are being used.
Currently works but is slow.
runs on linode with 8 GB RAM, 6 CPU Cores, 192GB SSD.

Goals

Insertion Speed: currently inserting new records takes LOT of time. we need to be able to sustain insert speed of 20/records per second.
Lookup: for newer data, past 3-4month. we query this quickly using location_id & barcode ordered by created_at DESC but for older data, its okay if it takes a while to run the query & return results.

Now,
what can we do to that could improve.  schema changes? dropping indexes? partitioning? better hardware? Looking for some advice.


texts: AWS RDS Post Optimisation after a huge delete

texts:
I'm using AWS RDS MySQL. All the tables are in InnoDB and enable innoDB_file_per_table.

One of my logging tables is growing too fast. I'd like to remove older than 1-week data. Even 1 week of data is 120GB. Every weekend this job will run and remove the old data.

I want to reclaim the disk space and update the statistics. OPTIMIZE table is really painful for us.

What are the optimizations I should perform after deleting a huge number of records?


texts: Shopping cart schema with multiple product tables

texts:
I have a shopping cart table used to store customer id and product id of their chosen items. However I have multiple product tables, for example cars, books, houses. I started out with one table but then realized using an auto increment key for the product tables might cause a problem, as I will not know which table to join the shopping cart with. I originally started out with one table so I did not have this problem before, but now I have to expand it to accommodate two additional product tables. I am looking for some help in how to restructure this schema to properly store products to the cart table.

Cars

╔══════════╦═══════════╦══════════╗
║ CarID    ║ Model     ║ Brand    ║
╠══════════╬═══════════╬══════════╣
║        0 ║ E 320     ║ Bmw      ║
║        1 ║ JR 505    ║ Audi     ║
╚══════════╩═══════════╩══════════╝

Books

╔══════════╦═══════════╦══════════╗
║ Book ID  ║ Author    ║ Title    ║
╠══════════╬═══════════╬══════════╣
║        0 ║ A.R       ║ Smithy   ║
║        1 ║ J.R.      ║ Hailey's ║
╚══════════╩═══════════╩══════════╝

Houses

╔══════════╦═══════════╦══════════╗
║ HouseID  ║ Address   ║ Town     ║
╠══════════╬═══════════╬══════════╣
║        0 ║ Belmont Av║ Bull Bay ║
║        1 ║ Cedar Drv ║ Cinclairs║
╚══════════╩═══════════╩══════════╝

Cart Table

╔══════════╦═══════════╦══════════╗
║ CartID   ║ memberID  ║ ProductID║
╠══════════╬═══════════╬══════════╣
║        0 ║ 3         ║ 1        ║
║        1 ║ 5         ║ 0        ║
╚══════════╩═══════════╩══════════╝


texts: sql query to format the parent child in a hierarchical list

texts:
I want to have the data displayed in the below parent child like relationship, where the first row should be parent row, and every subsequent row should be for child with null in all parent specific columns.

 99999999 has 2 dependents 22222222 and 33333333.

In the above the consumer 99999999 has 2 dependents 22222222 and 33333333.

I tried doing this with Lead and lag but that will need an order by in the CTE. I was thinking of looking at the previous ApplicationroductID, and would flag whenever I see that it has changed. So by doing that my I can flag the parent and Child. I could potentially get away with the order by problem by using a temp table, but that doesnt sound like a very good solution. Is there a better way of doing this that you guys can think of. I want to have the data to be in the above format.

select
    de.ID as ConsumerID
    ,dd.ID as DependentID,
    f.ApplicationroductID,
    LEAD(f.ApplicationroductID) OVER (Order by fep.ApplicationroductID) Lead,
    LAG(f.ApplicationroductID) OVER (Order by fep.ApplicationroductID) Lag
 from Fact f
 INNER JOIN DimEmp de
     ON fep.DimEmp_FK = de.ID
 INNER JOIN DimDep dd
     ON fep.DimDep_FK = dd.ID


texts: Genghis admin ui not working with mongodb 3.0.1 wired tiger

texts:
We recently updated to mongodb 3.0.1 using the wired tiger storage engine, and have been using the genghis php admin ui through the browser.  However, genghis is not completely working now, specifically it won't show collections available in the database.  It seems the php call $db->listCollections() returns 0 collections.  We are using the latest php mongo driver(1.6.5), and php 5.5.9.

Genghis will show collections for databases if we switch back to the mmap storage engine, so it looks like it might be something going on with how the php driver and listCollections() call works with the wired tiger storage engine?  There are no errors in the apache or mongodb logs when I try browsing the databases with genghis.

I tried searching online and couldn't find any related problems, has anyone had problems with wired tiger + php like this?


texts: Query against sys.schemas and sys.synonyms runs very slow for one user

texts:
Scenario: SQL Server 2014 (v12.0.4100.1)

.NET Service runs this query:

SELECT name, base_object_name
FROM sys.synonyms
WHERE schema_id IN (SELECT schema_id
                    FROM sys.schemas
                    WHERE name = N'XXXX')
ORDER BY name

...which returns about 6500 rows but it often times out after 3+ minutes. The XXXX above is not 'dbo'.

If I run this query in SSMS as UserA, the query returns in less than a second.

When run as UserB (which is how the .NET service connects), the query takes 3-6 minutes, and has the CPU% at 25% (of a 4 cores) the entire time.

UserA is a Domain Login in the sysadmin role.

UserB is a SQL Login with:

EXEC sp_addrolemember N'db_datareader', N'UserB'
EXEC sp_addrolemember N'db_datawriter', N'UserB'
EXEC sp_addrolemember N'db_ddladmin', N'UserB'
GRANT EXECUTE TO [UserB]
GRANT CREATE SCHEMA TO [UserB]
GRANT VIEW DEFINITION TO [UserB]

I can duplicate this in SSMS by wrapping the above SQL in an Execute as...Revert block, so the .NET code is out of the picture.

The execution plan looks the same. I diff'ed the XML and there are only minor differences (CompileTime, CompileCPU, CompileMemory).

IO Stats all show no physical reads:

Table 'sysobjvalues'. Scan count 0, logical reads 19970, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 1, logical reads 9122, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysclsobjs'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

XEvent waits status (for a ~3min query) are:

+---------------------+------------+----------------------+------------------------------+-----------------------------+
|      Wait Type      | Wait Count | Total Wait Time (ms) | Total Resource Wait Time (ms) | Total Signal Wait Time (ms) |
+---------------------+------------+----------------------+-------------------------------+-----------------------------+
| SOS_SCHEDULER_YIELD |      37300 |                  427 |                            20 |                         407 |
| NETWORK_IO          |          5 |                   26 |                            26 |                           0 |
| IO_COMPLETION       |          3 |                    1 |                             1 |                           0 |
+---------------------+------------+----------------------+-------------------------------+-----------------------------+

If I rewrite the query (in SSMS, I don't have access to the App Code) to

declare @id int
SELECT @id=schema_id FROM sys.schemas WHERE name = N'XXXX'
SELECT a.name, base_object_name FROM sys.synonyms a
WHERE schema_id = @id
ORDER BY name

then UserB runs at the same (fast) speed as UserA.

If I add db_owner to UserB, then, again, the query runs < 1 sec.

Schema created via this template:

DECLARE @TranName VARCHAR(20)
SELECT @TranName = 'MyTransaction'

BEGIN TRANSACTION @TranName
GO

IF NOT EXISTS (SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
        WHERE SCHEMA_NAME = '{1}')
BEGIN
    EXEC('CREATE SCHEMA [{1}]')
    EXEC sp_addextendedproperty @name='User', @value='{0}', @level0type=N'Schema', @level0name=N'{1}'
END
GO

{2}

COMMIT TRANSACTION MyTransaction;
GO

And {2} is, I believe, a list of Synonyms created in that schema.

Query Profile at two points into the query:

I've opened a ticket with Microsoft.

Also, we tried adding UserB to db_owner, and then DENYing all of the privileges we know that are associated with db_owner.  The result is a fast query.  Either we missed something (entirely possible), or there is a special check for the db_owner role.


texts: will a record set derived from jsonb_to_recordset always maintain the order of the items in a JSON array

texts: I want to be able to retrieve a jsonb array of objects as a record set. Maintaining the order of objects is critical. Will jsonb_to_recordset always return the record set in the order of the array of objects?


texts:
Using google doc, I am able to share my documents with others. I wonder how they implement it underlying in terms of DB design?

The simplest way I imagine is to use a joining table which keeps a many-to-many relationship between resource and accounts to share.

However, I wonder if there is any well-known pattern for this? If the account has hundred resources, then hundred joining tables seem not a scale way, as for each resource you have to write specific code. Is there any thing like RBAC(role based access control) for this sharing problem?


texts: Error while installing mysql database in linux server

texts:
I am trying to install MySQL database in linux system (Red Hat Enterprise Linux Server release 6.1 (Santiago))

I am following official mysql installation documentation from below link

https://dev.mysql.com/doc/refman/5.7/en/linux-installation-yum-repo.html#yum-repo-installing-mysql

But I am getting below error while running command

root@devdesk]# yum install mysql-community-server
Loaded plugins: downloadonly, product-id, refresh-packagekit, subscription-manager
Updating Red Hat repositories.
http://repo.mysql.com/yum/mysql-connectors-community/el/6/x86_64/repodata/repomd.xml: [Errno 14] PYCURL ERROR 22 - "The requested URL returned error: 403"
Trying other mirror.
Error: Cannot retrieve repository metadata (repomd.xml) for repository: mysql-connectors-community. Please verify its path and try again

How to resolve this error..? and get mysql installed in server.


texts:
I need to track license usage for software and the software does not have any sort of metric that we can use. The need for this comes when it is time to pay the maintenance quarterly and trying to split the cost to each department's budget based on how many users each department has.  We want to be able to see usage over the last week/month/year to get something that is automatic and a little less "I think, maybe...".

I want to store the data so that we can use it but I am not sure the based way to structure this

What I am currently working with is a command that I can run to get a list, and it has the who and the what and when they pulled the license.

I have two options that I can see (I am open to other suggestions as well):

Query every 5 minutes, and capture who,what software, and current timestamp and then put that all in a single record.  This will lead to a large amount of records being generated.
Query every 5 minutes and for each user/software combination check the db for an existing record that has a start and no stop and add a record if it does not exist and update a stamp if it does. Anything that does not get updated we close it with the current stamp. Basically a in/out system.

Again the goal is to see who was using what software and break that up by department to figure out the total usage for billing.

The user pool for this software is about 50 (max) and average about 30 concurrent.
Some some of the software just adds to the base software (like added features) so a single user can pull many licenses if the need is there.
For reporting we wont use it but it would be nice to see who is hogging the seats that we have a smaller number of.


texts: Improving the queries in a view to query custom types

texts:
I'll spare you the details, but I've got a situation where I'm going to be creating and managing a lot of custom types. (Postgres 11.4, ultimate deployment under RDS.) It looks like there isn't a system view to make reviewing types simple. Starting from some very helpful threads here, I bashed together a flattened view that lists each type's name, owner, schema, and attributes. I'm sure there's a way to get the same output with a simpler set of queries...but I don't know what they are. So I'm posting the code here in hopes that someone can improve it. And, if that doesn't happen, the current version may prove helpful to someone else eventually.

-- View: data.custom_types
DROP VIEW IF EXISTS data.custom_types;

CREATE OR REPLACE VIEW data.custom_types AS

with type_info as (
   select t.typrelid as type_oid,
          t.typowner::regrole::text as type_owner, -- ::text to make for easy searching
          n.nspname as type_schema,
          t.typname as type_name

     from pg_type t
left join pg_catalog.pg_namespace n on n.oid = t.typnamespace

    where (select c.relkind = 'c'
             from pg_catalog.pg_class c
            where c.oid = t.typrelid) and
                  not exists (select 1
                              from pg_catalog.pg_type el
                             where el.oid = t.typelem and
                                    el.typarray = t.oid) and
                  n.nspname not in ('pg_catalog', 'information_schema')
 )

-- Get the position, name, and type details of a custom type.
select type_info.type_owner,
       type_info.type_schema,
       type_info.type_name,
       pg_attribute.attnum as attribute_number,
       pg_attribute.attname as attribute_name,
       pg_type.typname as attribute_type

  from pg_attribute
  join pg_type   on (pg_attribute.atttypid = pg_type.oid)
  join pg_class  on (pg_attribute.attrelid = pg_class.oid)
  join type_info on (pg_attribute.attrelid = type_info.type_oid);

Note that this version of the query is deliberately not picking up custom types created by an extension. That's what I need here, but it other situations call for getting all of the types.

Then you can query or group by any of the columns. Just to show an example:

select * from custom_types where type_name = 'state_v1' order by attribute_number;

type_owner    type_schema    type_name    attribute_number  attribute_name    attribute_type
user_change_structure    api    state_v1    1               name              citext
user_change_structure    api    state_v1    2               abbr              citext
user_change_structure    api    state_v1    3               population        int8
user_change_structure    api    state_v1    4               total_sq_miles    float4
user_change_structure    api    state_v1    5               percent_land      float4
user_change_structure    api    state_v1    6               statehood_year    int2

It's also handy for GROUP BY and ARRAY_AGG queries.

 select type_schema,
        type_name,
        type_owner,
        count(*) as attribute_count,
        array_agg(attribute_name order by attribute_number) as attribute_names

   from custom_types
  where type_name like 'state_v%'

group by type_schema,
          type_name,
         type_owner

 order by type_name;

type_schema type_name   type_owner  attribute_count attribute_names
api state_v1    user_change_structure   6   {name,abbr,population,total_sq_miles,percent_land,statehood_year}
api state_v2    user_change_structure   8   {id,name,abbr,population,total_sq_miles,percent_land,statehood_year,capital}


texts: ACE.OLEDB.12.0 is visible on Linked Servers Provider, still can't import EXCEL file

texts:
I just don't know what to do anymore. I've been searching in a LOT of topics. I"ve downloaded tons of providers from microsoft.

I'm trying to import an Excel file to a database. I've been having this problem since ever. some servers it works, some don't. Now I have one that it doesnt and for my surprise is my test environment.

When task . Import data > DATA SOURCE: Microsoft excel I receive this error:

  TITLE: SQL Server Import and Export Wizard

  The operation could not be completed.

  ------------------------------ ADDITIONAL INFORMATION:

  The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local
  machine. (System.Data)

  ------------------------------ BUTTONS:

  OK

ok no problem let's install ACE OLEDB 12.

Got AccessDatabaseEngine_X64 and installed.

I can see it's working because Is on providers:

but then BAM! the same error.


texts:
I wish to pivot INFO2 into 2 columns.
INFO3 data should adjusted accordingly after the PIVOT.

The challenge is I also have a date column, which I want to take the MAX of.
The MAX(Date) should also be grouped by (lot_no, mcid).

Is there a way of doing this in one fell swoop?
Without resorting to temporary tables?


texts: Determining which SQL Server tables are accessed over a week?

texts:
I inherited a large, pretty-much undocumented SQL Server 2008 R2 general purpose database, used by dozens of different applications.

We know many of the tables are no longer in use.

Any suggestions on how to determine what tables are still being routinely accessed over a weeks time? Or how to best configure Profiler to gather that info?


texts: Local SSMS client is not connecting one particular server

texts:
My SSMS is not connecting to one particular server say X except X it is connecting to all the other servers. Could not find the root cause for this problem does anyone have idea here?

Guy sitting next to me can connect to X server. Only for me its not connecting.

Note: Before one hour I can connect to same X server.

Details of my ssms client

  Microsoft SQL Server 2012 - 11.0.5058.0 (X64)  May 14 2014 18:34:29
  Copyright (c) Microsoft Corporation Developer Edition (64-bit) on
  Windows NT 6.2  (Build 9200: )

Error am getting

Any help is highly appreciated.

I went to the command prompt and typed ping X. It's coming back with Request Timed Out 4 times. Server is not down; my neighbour can connect to same X server which is not connecting to me.


texts: send alerts based on attributes to users

texts:
there is users table.
users have one center, field. (centers & fields have many users)
users also can take courses.
so here is the relationships:
users 0..* -------- 1..1 centers
users 0..* -------- 1..1 fields
users 0..* -------- 0..* courses
the problem is I want send alerts to users based on their fields, centers and courses they took (or assign tasks to them. group or categorize dynamically by superusers input).
any idea will be appreciated! thanks.


texts: Is there any performance advantage in using CTE for ROW_NUMBER query

texts:
I have following queries to select latest order from each user. Is there any performance advantage in the query using CTE? For my current tables, both are giving same execution plan and time.

Note: I am using SQL Server 2012

DECLARE  @Person TABLE (PersonId INT, NameFirst VARCHAR(100), NameLast VARCHAR(100))
DECLARE @Order TABLE (OrderId INT, PersonId INT, OrderDateTime DATETIME)

--Query1
SELECT A.PersonID,A.NameFirst,A.NameLast, A.OrderID AS LastOrderID,A.OrderDateTime AS LastOrderDateTime
FROM
(
    SELECT P.PersonID,P.NameFirst,P.NameLast,O.OrderID,O.OrderDateTime,
           ROW_NUMBER() OVER(PARTITION BY O.PersonID ORDER BY O.PersonID,O.OrderDateTime DESC) AS RowNumber
    FROM @Person P
    LEFT OUTER JOIN @Order O
        ON P.PersonId = O.PersonId
)A
WHERE RowNumber = 1
ORDER BY PersonID

--Query2
;WITH CTE AS
(
    SELECT  P.PersonID,P.NameFirst,NameLast,O.OrderID AS LastOrderID,O.OrderDateTime AS LastOrderDateTime,
            ROW_NUMBER() OVER(PARTITION BY O.PersonID ORDER BY O.OrderDateTime DESC) AS RowNumber
    FROM @Person P
    LEFT OUTER JOIN @Order O
        ON P.PersonId = O.PersonId
)
SELECT A.PersonID,A.NameFirst,A.NameLast, A.LastOrderID, A.LastOrderDateTime
FROM CTE A
WHERE RowNumber = 1
ORDER BY PersonID


texts: Replication pull job on subscriber stopped running when removing article from publication

texts:
Version: SQL Server 2014 (Web edition)
Replication: Transactional

Came across an odd issue whereby the replication pull job on 10 of our 45 subscribers stopped running when removing article from corresponding publication.

This was completed through the GUI and didn't see any errors in distributor's SQL log.

Any ideas?


texts:
I have an application where each client gets their own database. This is for security, backup ease, and portability. It provides great flexibility and performance has been fine. I am up to 300+ databases and almost 20k tables. I use a 4gb ram server with this configuration. I use a database as a service so I don't actually have physical access to server; but can make changes and have root mysql access.

I am looking to move up the 8gb of memory as my innodb buffer pool is smaller than how much data I have in innodb; and I only have about 600-700mb free memory.

My host allows me to provision up to 32 GB of ram. At some point I will start adding 1 or more new database instances and change my application logic to have a lookup table to find which database to go to.

Right now I am trying to keep costs down and keep it simple so I don't have to re-enginer a bunch of stuff. (Not hard; would take about 2-3 days).

When is the right time to start scaling out instead of up? What issues might I run into if I keep scaling up?


texts: Is there a way to connect a Postgres 9 database client to a Postgres 8 system?

texts:
I a now work mainly with Postgres 9.5, but I alsoo need to connect to a number Postgres 8 databases connect them probably as different schemas, if that is possible.
Are foreign database wrappers the kind of thing which makes this possible, or is there some built in support in Postbres 9.5?


texts: How to support multiple updates in this trigger?

texts:
Below is the code of a trigger, basically it calls a function in case the record that is being updated does not have other row associated by it's Id in another table.
In a multiple row update this trigger fails, how can I edit it to make compatible?

declare @slugExists int =(select Id from [dbo].[UrlRecord]
                              where EntityId = (select Id from inserted)
                              and EntityName = 'Category')

    if(@slugExists is null)
    begin
        declare @slug nvarchar (200) = (select Name from inserted)
        EXEC @slug = dbo.GenerarSlug @str= @slug

        insert into [dbo].[UrlRecord] ([EntityId],[EntityName],[Slug],[IsActive],[LanguageId])
        values ( (select Id from inserted) ,'Category',@slug,1,0)
    end

EDIT

Based in comments, here is the code of the function GenerarSlug :

ALTER FUNCTION [dbo].[GenerarSlug]
(
    @str VARCHAR(500),
    @modificador VARCHAR(3) = NULL
)
RETURNS VARCHAR(500)
AS
BEGIN
DECLARE @IncorrectCharLoc SMALLINT
SET @str = LOWER(@str)
SET @IncorrectCharLoc = PATINDEX('%[^0123456789abcdefghijklmnopqrstuvwxyz ]%',@str)
WHILE @IncorrectCharLoc > 0
BEGIN
SET @str = STUFF(@str,@incorrectCharLoc,1,'')
SET @IncorrectCharLoc = PATINDEX('%[^0123456789abcdefghijklmnopqrstuvwxyz ]%',@str)
END
SET @str = REPLACE(@str,'  ','')
SET @str = REPLACE(@str,'   ','')
SET @str = REPLACE(@str,' ','-')
IF(@MODIFICADOR IS NOT NULL) SET @str = @str + @MODIFICADOR
RETURN @str
END

It transforms 'Especial Chárs' to 'especial-chrs'
The reason I put this in a function is that it will be called from a few triggers in database so I don't repeat it each time


texts: What is difference between internal and external load balancer listener with respect to azure (IaaS)

texts:
As per below MSDN blog, I see there are 2 ways we can create load balancer listener .

https://azure.microsoft.com/en-gb/documentation/articles/virtual-machines-windows-classic-ps-sql-int-listener/

But I am NOT finding exact scenario where I need to use them? I would like external applications connect to my SQL server which is highly available (AlwaysOn). which load balancer i should configure?

Article here http://clusteringformeremortals.com/2015/01/01/step-by-step-how-to-configure-a-sql-server-failover-cluster-instance-fci-in-microsoft-azure-iaas-sqlserver-azure-sanless/  says that,

  Once the cluster is configured, you will need to create the internal load balancer(ILB) which will be used for all client access. Clients that connect to SQL Server will need to connect to the ILB instead of connecting directly to the cluster IP address. If you were to try to connect to the cluster directly at this point you would see that you cannot connect to the cluster from any remote system. Even SQL Server Management Studio will not be able to connect to the cluster directly at this point

Update on How to configure it

I am using below script to create internal load balancer and it got succeeded.

       // Define variables
       $ServiceName = "XYZ"
       $AGNodes = "ABC01","ABC02"
       $SubnetName = "MYSUBNETNAME"
       $ILBStaticIP = "10.249.XXX.XXX"
       $ILBName = "AGListenerLB"

       // Create the ILB
       Add-AzureInternalLoadBalancer -InternalLoadBalancerName $ILBName -SubnetName $SubnetName -ServiceName $ServiceName -StaticVNetIPAddress $ILBStaticIP

       // Configure a load balanced endpoint for each node in $AGNodes using ILB
       ForEach ($node in $AGNodes)
       {
           Get-AzureVM -ServiceName $ServiceName -Name $node | Add- AzureEndpoint -Name "ListenerEndpoint" -LBSetName "ListenerEndpointLB" -Protocol  tcp -LocalPort 1433 -PublicPort 1433 -ProbePort 59999 -ProbeProtocol tcp -ProbeIntervalInSeconds 10 -InternalLoadBalancerName $ILBName -DirectServerReturn  $true | Update-AzureVM
       }

Once above script is successfull then we need to run the below script

   # Define variables
   $ClusterNetworkName = "Cluster Network 1" # the cluster network name (Use Get-ClusterNetwork on Windows Server 2012 of higher to find the name)
   $IPResourceName = "DEVAG_10.249.XXX.XXX" # the IP Address resource name
   $ILBIP = “10.249.XXX.XXX” # the IP Address of the Internal Load Balancer (ILB)

     Import-Module FailoverClusters

  If you are using Windows Server 2012 or higher, use the Get-Cluster Resource command. If you are using Windows Server 2008 R2, use the
  cluster res command. Both commands are commented out. Choose the one
  applicable to your environment and remove the # at the beginning of
  the line to convert the comment to an executable line of code.

     Get-ClusterResource $IPResourceName | Set-ClusterParameter -Multiple @{"Address"="$ILBIP";"ProbePort"="59999";"SubnetMask"="255.255.255.255";"Network"="$ClusterNetworkName";"EnableDhcp"=0}
     # cluster res $IPResourceName /priv enabledhcp=0 address=$ILBIP probeport=59999  subnetmask=255.255.255.255


texts: Filling in missing dates with values from the month before


texts: In Sql2012 Management Studio, there are separate options for "T-SQL90" and "Transact-Sql". My .sql files always seem to open in "Transact-Sql". My question is what is a "T-SQL90" file, how does it differ from a regular transact sql file, ...and how does Management Studio automatically decide what type of file it is? (different extension? if so, what is it?)


texts:
I have a table with around 400 GB data I want to copy some data to some other temporary database to make some more space. Can you please give me a efficient way to do that?

My DBMS is SQL server 2014 standard edition.


texts:
We are currently having a problem loading arabic into a SQL Server 2008 database.

We want to load
بِسْمِ اللَّهِ الرَّحْمَٰنِ الرَّحِيمِ

but in the database it changes to

بِسْمِ اللَّهِ الرَّحْمَ?نِ الرَّحِيمِ

with a ? added,

We have set the collation Arabic_CI_AI_KS_WS and SQL_Latin1_General_CP1256_CS_AS

and neither of these work..... any suggestions?


texts:
We have a sharded cluster with 3 shards. The version is: 3.6.13. The users of the prod. database (1,8 TB sized) complain about an issue.
System works normally but after sometime it stucks and doesn't respond. During that time we don't see any long running queries. Everything seems normal in the log files.
But when we track the network the requests are coming to Mongos, Mongos sends them to mongod but shards don't send back anything in network level.
The single qlue that I catch is about oplog entries. When I took a dump backup with oplog, in dump log it has "dumped 7757639 oplog entries"
In the dumps I took last week there was only 1 dumped entry. Is there a problem with the replication that causes waits?

When I check the replication status, it seems there is no problem.
Thank you for your replies.


texts: Audit everything coming from an ip adress in an Oracle database

texts: I need to audit all activity coming from a specific ip adress. Whats the best method to do this in an Oracle Database 19c?


texts: Are InnoDB files binary compatible between Windows and Linux?

texts:
I have seen many posts about recovering InnoDB files.

The versions I have are:

Windows: 5.6.17
Linux 5.0.96 (I cannot upgrade this at this time)

If I create a new InnoDB file on the Windows machine, can it be copied to the Linux machine? Are they binary compatible?


texts:
I have the following query

Select  Pt.PRODID, PT.INVENTREFID,  Inv.ItemName, PT.ItemID, Configid,  Pt.QTYSCHED,
PT.DLVDATE,  Pt.CREATEDDATETIME, pt.SCHEDEND,
 CASE Left(PT.INVENTREFID, 3)
     WHEN 'SJB' THEN ST.SALESNAME
     WHEN 'WJB' THEN 'Sub - Assembly'
     ELSE 'Stock'
  END as CustomerName
from Dynamicsv5Realtime.dbo.PRODTABLE PT
Join Dynamicsv5Realtime.dbo.Inventdim ID
On PT.InventdimId = ID.InventdimID
and PT.Dataareaid = ID.dataareaid
Join Dynamicsv5Realtime.dbo.INVENTTABLE Inv
On Inv.itemid = PT.ItemId
Left Join Dynamicsv5Realtime.dbo.SALESTABLE ST
ON ST.SalesId = PT.INVENTREFID
and ST.Dataareaid = PT.dataareaid
where pt.PRODSTATUS in(2,3,4)
and PT.DATAAREAID = 'AJB'
Order by 7

when I was looking to improve it I came up with the following index

CREATE NONCLUSTERED INDEX [INDEX_2]
ON [dbo].[PRODTABLE] ([INVENTREFID],[DATAAREAID],[INVENTDIMID],[PRODSTATUS])
INCLUDE ([PRODID],[ItemID],[QTYSCHED],[DLVDATE],[CREATEDDATETIME],[SCHEDEND])

However this was not as efficient as the following which the missing index DMV came up with

CREATE NONCLUSTERED INDEX [INDEX_1]
ON [dbo].[PRODTABLE] ([DATAAREAID],[PRODSTATUS])
INCLUDE ([ITEMID],[QTYSCHED],[DLVDATE],[SCHEDEND],[INVENTREFID],[PRODID],[INVENTDIMID],    [CREATEDDATETIME])

What I am wondering is when is it more efficient to put columns in the index itself rather than in the Include part of the index?

May thanks


texts: Repeat all base table rows for EACH and don't aggregate the NULL from the join table


texts: How do I rewrite my query to use less temporary space?

texts:
I'm using Postgres 9.5.  I want to identify (and then delete) rows from my table that are "duplicates" in the sense that they contain three fields that are the same, namely

my_object_id
name
time_in_ms

The table is defined like so

myproject_production=> \d my_object_times;
                               Table "public.my_object_times"
      Column       |            Type             |              Modifiers
-------------------+-----------------------------+-------------------------------------
 first_name        | character varying           |
 last_name         | character varying           |
 time_in_ms        | bigint                      |
 created_at        | timestamp without time zone | not null
 updated_at        | timestamp without time zone | not null
 name              | character varying           |
 racer_id          | character varying           |
 age               | integer                     |
 city              | character varying           |
 state_id          | integer                     |
 country_id        | integer                     |
 my_object_id           | character varying           | not null
 id                | character varying           | not null default uuid_generate_v4()
Indexes:
    "my_object_times_pkey" PRIMARY KEY, btree (id)
    "index_my_object_times_on_country_id" btree (country_id)
    "index_my_object_times_on_my_object_id" btree (my_object_id)
    "index_my_object_times_on_state_id" btree (state_id)
    "my_object_times_name_idx" btree (upper(name::text) text_pattern_ops)
    "my_object_times_rev_name_idx" btree (reverse(upper(name::text)) text_pattern_ops)
Foreign-key constraints:
    "fk_rails_0fe1d25967" FOREIGN KEY (country_id) REFERENCES countries(id)
    "fk_rails_a8771b3575" FOREIGN KEY (state_id) REFERENCES states(id)
    "fk_rails_ba656ceafa" FOREIGN KEY (my_object_id) REFERENCES my_objects(id) ON DELETE CASCADE
Referenced by:
    TABLE "user_my_object_time_matches" CONSTRAINT "fk_rails_2e7860946c" FOREIGN KEY (my_object_time_id) REFERENCES my_object_times(id) ON DELETE CASCADE
    TABLE "my_objects" CONSTRAINT "fk_rails_dda3297b57" FOREIGN KEY (linked_my_object_time_id) REFERENCES my_object_times(id) ON DELETE CASCADE

I thought this query would do the job

select rt1.id, rt1.name
FROM my_object_times rt1,
     my_object_times rt2
where rt1.my_object_id = rt2.my_object_id
  and rt1.name = rt2.name
  and rt1.time_in_ms = rt2.time_in_ms
  and rt1.id > rt2.id;

But I guess I have so much data, the above query is dying with the below error:

ERROR:  could not write block 1862514 of temporary file: No space left on device

This is after pointing my temporary space (pgsql_tmp directory) at a partition that gives it 20 GB of temp space.  So my question is, how can I rewrite the above so that it does not use as much temp space?  That is fine if it takes the query a really long time to run.


texts:
I use AMPPS for localhost server purpose. I was working on one of my client site on localhost.

When I start working the other day, it suddenly start giving me such error

table does not exist Even though I can clearly see tables on left hand side bar as you can see in attached image.

I tried lots of ways mentioned on net. But I am unable to solve the issue. I don't know what exactly happened there.

Please help me out. Any help will do.

Thanks,
Mahesh


texts:
I want to connect to SQL Server via Internet:

I turned off the firewall
I enabled the the DMZ in the router, host address is 192.168.1.24 which is the SQL Server LAN IP
I open port in the router for both SQL Server and SQL Server Browser (1433 - 1434)
I checked the open port via (canyouseeme) website, and the result is success for 1433 and error for 1434  (I don't know the reason for the error)
I made the necessary configuration for the TCP protocols
I checked that the server is allowed remote connection

But I failed to connect to the server via Internet.

My LAN IP is 192.168.1.24
The instance is default instance
My public IP is 95.218.156.146 (for example)

When I type (192.168.1.24) in the server name text-box I succeed to connect to the server,
but I fail to connect when I type 195.218.156.146  or 95.218.156.146 ,1433

Note:

When I put the public IP in the brower then the router page appears (meaning  that the public IP is correct)
I didn't find port forwarding in my router, but I have configured the DMZ and port mapping


texts: Same query same data different server different query plan

texts:
I have a query that hits a view, which references other views etc...several levels down. The query plan is a bit complex.

I run the query on 2 different servers. On both servers, the number of rows in referenced is roughly the same and the query returns 3 rows in both environments..

In production, sql server chooses a query plan that looks to create a resultset of one of the underlying views without applying the join clause first. this results in 8x more page reads.

In QA environment, sql server applies the join clause first and minimizes the rows returned by that subtree. the subtree in prod returns 42k actual rows and in QA 2 actual rows.

What would cause sql server to choose a less optimal plan?

QA is sql 2012 sp3
Prod is sql 2012 sp1
no physical reads in either case.
prod stats io shows 42k logical reads on worktable. I am assuming this is the resultset from that subtree.
QA does not show this.


texts: How can you view the Query Store settings for a database?

texts:
I see that you can use notion  like the following to set the query store configuration:

ALTER DATABASE <database name>
SET QUERY_STORE (SIZE_BASED_CLEANUP_MODE = AUTO);

But how can I check to see what the current value for SIZE_BASED_CLEANUP_MODE is?


texts: Storing companies' working hours in Postgres

texts: I have table with millions of companies data (name, address, phones etc) in my Postgres database. I want to store working hours (example: mon-fri: 9:00-12:00, 13:00-18:00; sat: 9:00-13:00). How can I store it to be able to find all companies opened in requested time?


texts: What is the impact of MAX_DISPATCH_LATENCY while configuring EXTENDED EVENT?

texts: By default, MAX_DISPATCH_LATENCY is set to 30 seconds. I chose FILE as a target and write all captures there. I thought that if I write all captures into file, why would then I lower down MAX_DISPATCH_LATENCY to 1 second and by this way prevent captures from staying in the memory for the max duration of 30 seconds. What is the impact of lowering down MAX_DISPATCH_LATENCY?


texts: Transactional Replication - Publications with same Name on the same Domain

texts:
I'm using Transactional Replication and I've currently got three 2008r2 instances Publisher/Distributor/Subscriber.

I need to put three more 2008r2 instances on the same domain in the exact same configuration.

I'll be changing the Publisher Names, but I'd like to avoid changing the Publication Names.

I'm pretty sure this is safe to do, but in the past I've found replication to be "problematic".

Our set-up includes little quirks such as Indexed Views materialised as Tables and I also have concerns about Orphan meta-data lurking under the bonnet.

Has anybody had any problems with duplicate Publication Names and strongly advises against them? Any shared wisdom would be much appreciated.


texts: Add columns to all tables in a database if the columns don't exist

texts:
I have to implement auditing in a database with many (50+) tables. I am looking to add the user name and the time when the user altered the record as columns to all tables. However, some tables have the columns already, so I don't need to add the columns to all tables. Assuming the column names are DateTime_Table and UserName_Table, how can I add DateTime_Table column and UserName_Table columns to all tables in the database if the columns don't exist?

I saw this loop for Postgres but I need help in SQL Server.


texts: Primary key guarantees: duplicates and nullity

texts:
Oracle allows primary keys to have duplicate and null values.
Using this capability isn't a particularly good idea, but it implies that some of what most developers consider a primary key's guarantees (non-null, unique) are not really guarantees.

CREATE TABLE oracle_guarantees (
    ID NUMBER(9,0),
    NAME VARCHAR2(50 BYTE),
    breed NVARCHAR2(100)
);

INSERT INTO oracle_guarantees VALUES (1, 'Fuzz Head', 'Tabby');
INSERT INTO oracle_guarantees VALUES (2, 'Fluffy Thing', 'Mix');
INSERT INTO oracle_guarantees VALUES (2, 'Fluffy Thing', 'Mix');
INSERT INTO oracle_guarantees VALUES (3, 'Tiger', 'Tabby');
INSERT INTO oracle_guarantees VALUES (4, 'Fur Beast', 'Bengal');
INSERT INTO oracle_guarantees VALUES (5, 'Karate', 'Japanese Bobtail');
INSERT INTO oracle_guarantees VALUES (6, 'Chairman Meow', 'Chinese Harlequin');
INSERT INTO oracle_guarantees VALUES (NULL, 'No Cat', 'No breed');

CREATE INDEX oracle_guarantees_pk ON oracle_guarantees (ID);
ALTER TABLE oracle_guarantees ADD CONSTRAINT oracle_guarantees_pk PRIMARY KEY (ID) DISABLE KEEP INDEX;
ALTER TABLE oracle_guarantees MODIFY CONSTRAINT oracle_guarantees_pk ENABLE NOVALIDATE;

SQLite PKs also allow one null but not duplicate values.

Is it possible to have constraints marked as primary keys which have duplicate values or null values in Microsoft SQL or Postgres?

In other words, can I absolutely rely on PK uniqueness and non-nullity within the documented feature set (ignoring cases like manually edited data files, bugs, or modified server source code)?

Further clarification:
I suspect that DBAs and developers work a little more differently than I thought.

Thought exercise:
A developer needs to uniquely identify rows on any table (the table structure is unknown at compile time).
Oracle's documentation on primary key constraints comes up in a search and says:

  A primary key constraint combines a NOT NULL constraint and a unique
  constraint in a single declaration. That is, it prohibits multiple
  rows from having the same value in the same column or combination of
  columns and prohibits values from being null.

The developer then looks for a means to find the primary keys on any table at run time. They probably come across a question like this: https://stackoverflow.com/questions/9016578/how-to-get-primary-key-column-in-oracle

The top voted answer's query yields:

I believe that any reasonable non-DBA person would, at this point, conclude that the ID column cannot have NULL or duplicate values. This conclusion is not true.

Is it Oracle's fault? No.
Is the table properly designed? No.
Are the statements to create such a table complex? Immaterial.
Is the Id column a "real" primary key? Maybe not, but this is more a philosophical matter. ID is listed (and is shown as
"ENABLED") by the query in the top-rated SO answer to the linked question above. Perhaps that question needs to add a check for validation, but I have never once seen anyone check for this in code, nor do any answers in that thread or related threads I have found.

Ergo, in the real world, an arbitrary table can have a primary key (by definition of all_constraints.constraint_type) which has duplicate values. And NULL values.

I now know that software must also ensure that the constraint is validated. Excellent! That provides the guarantee I need.

The question is: Is it possible to have a primary key (as defined by DBMS metadata) that has NULL or duplicate values in PostgreSQL or Microsoft SQL?


texts:
I have a MongoDB for storing result like this

{_id, type, imei, lat, lng, spd, dir, time}
{_id, type, imei, lac, cid, time}

If type is f first one is used and if it is l second one is used.

Here is the indexes:

"indexSizes" : {
    "_id_" : 340358704,
    "imei_1" : 614508160,
    "type_1" : 183616608,
    "type_1_imei_1" : 349793808
},

Collection has about 10 million documents. I have many queries on type and imei, and for now only types of f are used in applications.

This is a line from log file

Sun Aug 31 19:14:39.402 [conn782250] query xxyyxx.locs query: { $query: { $and: [ { imei: "359710042581612" }, { type: "f" } ] }, $orderby: { time: -1 } } ntoreturn:1 ntoskip:0 nscanned:140455 scanAndOrder:1 keyUpdates:0 numYields: 59 locks(micros) r:38456361 nreturned:1 reslen:159 19727ms

This took about 20 seconds, too much.

I need to optimize my collection more. This is used in a web application and sometimes queries like this are performed for 10 imeis, taking about 200 seconds, which may lead to a timeout. This is performed async (ajax) but still too much waiting time.

Any help from experts here is more than welcome.

Also, if you think I missed anything, please do request more information.

Thanks in advance

BTW, I'm using PHP for web part (Laravel framework and https://github.com/jenssegers/Laravel-MongoDB)

EDIT: Here is the explain (REMOVED)

EDIT2: After creating better indexes:

MongoDB shell version: 2.4.9
connecting to: test
> use gpstracker
switched to db gpstracker
> db.locs.stats()
{
    "ns" : "gpstracker.locs",
    "count" : 10137188,
    "size" : 1400944384,
    "avgObjSize" : 138.19852053646434,
    "storageSize" : 1580060672,
    "numExtents" : 19,
    "nindexes" : 2,
    "lastExtentSize" : 415145984,
    "paddingFactor" : 1,
    "systemFlags" : 0,
    "userFlags" : 0,
    "totalIndexSize" : 826806176,
    "indexSizes" : {
        "_id_" : 359997456,
        "type_1_imei_1_time_-1" : 466808720
    },
    "ok" : 1
}
> db.locs.find({ $and: [ { imei: "359710042581612" }, { type: "f" } ] }).explain()
{
    "cursor" : "BtreeCursor type_1_imei_1_time_-1",
    "isMultiKey" : false,
    "n" : 150158,
    "nscannedObjects" : 150158,
    "nscanned" : 150158,
    "nscannedObjectsAllPlans" : 150158,
    "nscannedAllPlans" : 150158,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 329,
    "nChunkSkips" : 0,
    "millis" : 128173,
    "indexBounds" : {
        "type" : [
            [
                "f",
                "f"
            ]
        ],
        "imei" : [
            [
                "359710042581612",
                "359710042581612"
            ]
        ],
        "time" : [
            [
                {
                    "$maxElement" : 1
                },
                {
                    "$minElement" : 1
                }
            ]
        ]
    },
    "server" : "CDSX001:27017"
}


texts: Questions on Availability Group Readable Secondary

texts:
Recently I encountered an issue in my SQL server. Please advise the concept and mechanism behind the AlwaysOn cluster.

Background:
 We built up a AlwaysOn cluster with 1 primary node dbs1 and 1 secondary node dbs2. Given that they configured both readable secondary as "read-intent only". They have also created the read-only routing list. Every connections specifying "ApplicationIntent=ReadOnly" will be redirected to dbs2.

Issue:
 The same agent job ( for insert data from mssql to MySQL) running on dbs2 is unstable - suddenly succeed and suddenly fail. For the success case, the agent session will be displayed on dbs1 instead of dbs2.

For failed case, it displayed an error that

  "Unable to connect to SQL Server '(local)'"   -- Agent Job History

  "the Target database is in an availability group and is currently accessible for connections when the application intent is set to read only".   -- SQL Error Log

But when we changed the readable secondary to be "yes", the agent job can always run successfully with the session on dbs2.

My Questions:

1) What is the behaviour of Read-intent only? Why the agent job failed?

2) Even it succeed, why the session is on dbs1 instead of dbs2, given that the agent job was set on dbs2? Does it relate to insert/delete/update command in the script?

3)What are the difference between read intent only and option yes on readable secondary? I have tested that the readonly routing in latter option still valid after specifying "ApplicationIntent= readonly"

I am really curious of their mechanisms. Please feel free to comment and discuss. Thanks.

Additional Information (newly update on 4 Aug 2017):

Below is my agent job script. Can I specify "ApplicationIntent=ReadOnly" on it? And I am confused that this T-SQL did not write any data in SQL Server table but write on another MySQL database. Why the availability group don't let me pass?  Again, special thanks to Ben and SqlWorldWide's comment and feedback!

EXECUTE ( 'TRUNCATE TABLE MySQL_Table' ) AT MySQL_Server;

INSERT INTO MySQL_Server...MySQL_Table  (c1,c2,c3)  SELECT c1,c2,c3 FROM [dbs2].[SQL_Database].[dbo].[SQL_Table];


texts: Converting SalesForce IDs for Performance Reasons

texts:
I'm writing code for a data warehouse that extracts information from SalesForce.  The format of the SalesForce IDs is like so:

001n000000UELLJAA5

Where each digit is a base-62 value denoted by:

a-z (26 values, case-sensitive)
A-Z (26 values, case-sensitive)
0-9 (10 values)

26+26+10 = a total of 62 values per digit

I want to convert these into a format that can be efficiently indexed by Postgres for thousands of rows.

I have gotten this far:

select id, array_to_string(array_agg(ascii),'','0')::decimal
FROM (
       SELECT id, ascii(regexp_split_to_table(id, '')) from example_schema.example_table
     ) x
group by id

but it seems super inefficient and also may be hard to reverse in some cases because there's no delimiter between the codes.

Is using a decimal type as an ID/identity efficient?
Is there an easier way to convert these to a numeric value that can still be used as a primary key and is a reversible operation if needed?

Here's the query plan:

GroupAggregate  (cost=109862.92..130267.92 rows=742000 width=51)
  Group Key: account.id
  ->  Sort  (cost=109862.92..111717.92 rows=742000 width=23)
        Sort Key: account.id
        ->  Result  (cost=0.00..14875.99 rows=742000 width=23)
              ->  ProjectSet  (cost=0.00..3745.99 rows=742000 width=51)
                    ->  Seq Scan on account  (cost=0.00..30.42 rows=742 width=19)

Example output:

001n000000ShgGbAAJ  746565987110310483484848484848110494848
001n000000SIZE7AAP  4848491104848484848488373906955656580
001n000000Sj3NCAAZ  48906565677851106834848484848481104948
001n000000SJK1sAAH  48484911048484848484883747549115656572

They seem out of order which will cause issues with reversing the operation


texts: New transactions during a Sybase "dump database" command

texts:
I have a Sybase 15.5 database which is backed up every night using the DUMP DATABASE command.  The process takes about 30 minutes.

What happens to the transactions that occur within those 30 minutes?  Are they included in the backup or ignored?


texts: Determine least number of columns required for unique (clustered) index

texts:
I'm in the situation where I am encouraged to tune an existing database and in the process either introduce or remove indexes as seem best fit.

I was looking at a certain table and noticed that the Database Tuning Adviser had been previously used, due to the fact that a lot of the indexes began with _dta_..... (Hooray!)

I had a glace at the Clustered Index on said table

CREATE CLUSTERED INDEX [_dta_index_TABLENAME_c_5_1970822083__K6_K8] ON [dbo].[TABLE_NAME]
([register] ASC, [deleted] ASC)
WITH (
         PAD_INDEX = OFF,
         STATISTICS_NORECOMPUTE = OFF,
         SORT_IN_TEMPDB = OFF,
         DROP_EXISTING = OFF,
         ONLINE = OFF,
         ALLOW_ROW_LOCKS = ON,
         ALLOW_PAGE_LOCKS = ON
     )
     ON [PRIMARY]
 GO

So the index isn't unique and probably has a very special distribution in the histogram. Both columns register and deleted are defined as NULL in the table DDL:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TABLE__NAME](
    [delete]      [smallint] NULL,
    [timestamp]   [int] NULL,
    [root_id]     [int] NULL,
    [object_id]   [int] NULL,
    [object_type] [int] NULL,
    [register]    [int] NULL,
    [reg_type]    [int] NULL,
    [deleted]     [int] NULL
) ON [PRIMARY]

GO

...which got me thinking.

Question

How can I determine the least required number of columns to create an UNIQUE index with the following premises:

Seeing as delete will contain either a 1 or 0 I omitted this column from my train of thoughts
I don't know the data
I do know the number of records is 58'934'964 (and growing by 20 every second)
I do know the distinct counts of each column:

delete      = 1
timestamp   = 29'030'385
root_id     = 1'744'414
object_id   = 58'934'823
object_type = 33
register    = 9'229'854
reg_type    = 8
deleted     = 723'952

What I tried

I had a look at the sys.dm_db_missing_index_details DMV to determine if SQL Server knows when to create an UNIQUE index.

select

sddmid.index_handle,
db_name(sddmid.database_id),
so.name,
sddmid.object_id,
sddmid.equality_columns,
sddmid.inequality_columns,
sddmid.included_columns,
sddmid.statement

from sys.dm_db_missing_index_details sddmid
join sys.objects as so
    on so.object_id = sddmid.object_id
where database_id = db_id('DATABASE')
and so.name = 'TABLE_NAME'

But no details in that view.

Possible Solution

Would I really have to perform a statement over all columns like this:

select
-- distinct single column
count(distinct delete), count(distinct timestamp), count(distinct root_id),...
-- distinct double column
count(distinct delete,timestamp), count(distinct delete,root_id), ...
--- distinct triple column
count(distinct delete,timestamp,root_id), count(distinct delete,timestamp,object_id), ...
--- distinct quadruple column
count(distinct delete,timestamp,root_id,object_id), ...
...
from TABLE_NAME

until I have found a combination that is distinct/unique?

I guess there is a better solution...


texts: Does MySQL support querying for the current running transaction id

texts:
PostgreSQL supports retrieving the current running transaction id using a query like this:

select txid_current();

Does MySQL have any such equivalent?


texts: How do you grant a user xp_cmdshell, sp_OACreate, sp_OAmethod, and sp_Destroy on SQL server 2008? What is the prons and cons of granting this permission? Please let me know. Thank you!


texts:
Please help me figure out why postgresql isn't playing nice with updating.  I can't find anything on SO or Google that remedies this problem.  This is a production server and a distribution reinstall is not really an option.  apt-get install -f and apt-get autoclean/check will tell you to use apt-get install -f.

root@vps1484:/home# apt-get install -f
Reading package lists... Done
Building dependency tree
Reading state information... Done
Correcting dependencies... Done
The following package was automatically installed and is no longer required:
  libaio1
Use 'apt-get autoremove' to remove it.
The following extra packages will be installed:
  postgresql-9.1
Suggested packages:
  oidentd ident-server locales-all
The following packages will be upgraded:
  postgresql-9.1
1 upgraded, 0 newly installed, 0 to remove and 14 not upgraded.
1 not fully installed or removed.
Need to get 0 B/4317 kB of archives.
After this operation, 193 kB disk space will be freed.
Do you want to continue [Y/n]? y
(Reading database ... 98727 files and directories currently installed.)
Preparing to replace postgresql-9.1 9.1.13-0ubuntu0.13.10 (using .../postgresql-9.1_9.1.15-0ubuntu0.12.04_amd64.deb) ...
 * Stopping PostgreSQL 9.1 database server                                                                                [ OK ]
Unpacking replacement postgresql-9.1 ...
dpkg: error processing /var/cache/apt/archives/postgresql-9.1_9.1.15-0ubuntu0.12.04_amd64.deb (--unpack):
 trying to overwrite '/usr/share/postgresql/9.1/man/man1/pg_basebackup.1.gz', which is also in package postgresql-client-9.1 9.1.13-0ubuntu0.13.10
Errors were encountered while processing:
 /var/cache/apt/archives/postgresql-9.1_9.1.15-0ubuntu0.12.04_amd64.deb
E: Sub-process /usr/bin/dpkg returned an error code (1)
root@vps1484:/home# sudo rm /usr/share/postgresql/9.1/man/man1/*
root@vps1484:/home# sudo rm /var/cache/apt/archives/*
rm: cannot remove '/var/cache/apt/archives/partial': Is a directory
root@vps1484:/home# sudo rmdir /var/cache/apt/archives
rmdir: failed to remove '/var/cache/apt/archives': Directory not empty
root@vps1484:/home# apt-get install -f
Reading package lists... Done
Building dependency tree
Reading state information... Done
Correcting dependencies... Done
The following package was automatically installed and is no longer required:
  libaio1
Use 'apt-get autoremove' to remove it.
The following extra packages will be installed:
  postgresql-9.1
Suggested packages:
  oidentd ident-server locales-all
The following packages will be upgraded:
  postgresql-9.1
1 upgraded, 0 newly installed, 0 to remove and 14 not upgraded.
1 not fully installed or removed.
Need to get 4317 kB of archives.
After this operation, 193 kB disk space will be freed.
Do you want to continue [Y/n]? y
Get:1 http://ca.archive.ubuntu.com/ubuntu/ precise-security/main postgresql-9.1 amd64 9.1.15-0ubuntu0.12.04 [4317 kB]
Fetched 4317 kB in 0s (5940 kB/s)
(Reading database ... 98727 files and directories currently installed.)
Preparing to replace postgresql-9.1 9.1.13-0ubuntu0.13.10 (using .../postgresql-9.1_9.1.15-0ubuntu0.12.04_amd64.deb) ...
 * Stopping PostgreSQL 9.1 database server                                                                                [ OK ]
Unpacking replacement postgresql-9.1 ...
dpkg: error processing /var/cache/apt/archives/postgresql-9.1_9.1.15-0ubuntu0.12.04_amd64.deb (--unpack):
 trying to overwrite '/usr/share/postgresql/9.1/man/man1/pg_basebackup.1.gz', which is also in package postgresql-client-9.1 9.1.13-0ubuntu0.13.10
Errors were encountered while processing:
 /var/cache/apt/archives/postgresql-9.1_9.1.15-0ubuntu0.12.04_amd64.deb
E: Sub-process /usr/bin/dpkg returned an error code (1)
root@vps1484:/home# >


texts: Clarification on MySQL Workbench Options File Configuring

texts:
When in the MySQL Workbench tool, configuring the "Options File," I see a drop-down in the lower right corner with the options:

client
mysql
mysqld

By default, 'mysqld' is selected and I see my configuration file's (which is loaded and in use) absolute path, opposite (left) of the aforementioned drop-down.  When I alternate between the different drop-down options, the fields change in the various tabs of options that are modifiable.  I expect this behavior.  However, what I don't understand is what the 3 drop-down options are for, overall.  I see the 'Apply' and 'Discard' button, so I'm under the assumption they're there for a reason.  I don't know what the differentiation is intended for or how it would impact my server if I were to select versus the other and click Apply.

Can someone explain to me what these are for and the impact they have?  I've tried researching this issue, already, but have had no luck with any explanation to this.

Thank you!

Windows 7 Pro (Server)
MySQL 5.6.20 (64-bit)
MySQL Workbench 6.2.3


texts: Deployment Manager Vs DBA Cage Match On Pushing Releases To Prod

texts:
Our company has now hired a full time release engineer for the MS Windows side and we were thinking of a release management process for MS SQL Server.

IF we have confidence in their abilities to deploy SQL Server scripts, is it common to just have the release manager do it or is it something that the DBAs typically do?  Our enterprise is growing fast and our DBA's are somewhat overloaded (surprise surprise what else is new) but we can hire more, but that's another matter.

How do you guys manage this?  Do you allow a release manager to have access to prod to roll out changes?  Do you take away rights and turn them on when they need to release?  I'm thinking I will give them access to a sproc that gives them prod access for an hour, but it logs who calls it.

Or am I totally off, and this is something a DBA should always manage?

Any thoughts would be appreciated!

Edit:

Update:
Also what happens when we encounter anomoloies?  For example, a dev stated that 'these tables should match this other environment (by environment I mean customer prod environment, not qa/stage/etc.)'.  Usually they would do a spot check.  I did a checksum and noticed issues which ended up being just whitespace issues.  In a case like this, do we push it back to the release manager/qa person to fix after doing basic troublehshooting?

Another example:  We have scripts by about 20 developers, sometimes they are dependent on each other.  The ordering of the script was wrong.  I can't keep up with 20 developers work and also manage the data, but after some troubleshooting we discovered the issue and changed the order.  Is this something the DBA should typically be deeply involved in or is it fair after basic testing and look over, we send it back to the release manager and devs to fix?


texts: PostgreSQL: Performance: ORDER BY an indexed column is slowing down the query performance

texts:
SELECT s.id, s.name, s.updated_at, COUNT(*) OVER ()
FROM screens s
INNER JOIN theatres t ON s.theatre_id = t.id
LEFT JOIN company_screen_mappings AS csm ON csm.screen_id = s.id AND csm.is_deleted = FALSE AND csm.company_id = 'f5003aa3-3621-498a-ab0f-526706fda88f'
WHERE 1=1 ORDER BY s.name asc  LIMIT 50 OFFSET 0

Here is the query plan:
https://explain.depesz.com/s/UC9B
Here are the indexes in the associated tables
CREATE INDEX idx_screens_name ON public.screens USING btree (name);
CREATE INDEX idx_screens_number ON public.screens USING btree (((data ->> 'number'::text)));
CREATE INDEX idx_screens_tags ON public.screens USING gin (tags);
CREATE INDEX idx_screens_theatre_id ON public.screens USING btree (theatre_id);

CREATE INDEX idx_theatre_companies ON public.theatres USING gin (company_ids);
CREATE INDEX idx_theatres_chain_name ON public.theatres USING btree ((((data -> 'chain'::text) ->> 'name'::text)));
CREATE INDEX idx_theatres_city_name ON public.theatres USING btree (((((data -> 'address'::text) -> 'city'::text) ->> 'name'::text)));
CREATE INDEX idx_theatres_country_name ON public.theatres USING btree (((((data -> 'address'::text) -> 'country'::text) ->> 'name'::text)));
CREATE INDEX idx_theatres_name ON public.theatres USING btree (name);
CREATE INDEX idx_theatres_province_name ON public.theatres USING btree (((((data -> 'address'::text) -> 'province'::text) ->> 'name'::text)));
CREATE INDEX idx_theatres_qwc_is_active ON public.theatres USING btree (qwc_is_active);
CREATE INDEX idx_theatres_tags ON public.theatres USING gin (tags);

CREATE UNIQUE INDEX company_screen_mappings_key ON public.company_screen_mappings USING btree (company_id, screen_id) WHERE (is_deleted = false);

The moment I remove ORDER BY s.name the query is considerably fast.
However, s.name is indexed, why is this happening?
Is there a way I could optimize the query?
Please suggest.
I need the result ordered by name.
I could notice that removing COUNT(*) OVER() greatly improves the query execution time. But I need the COUNT as well.


texts: Sudden High CPU Usage On SQL Server 2008R2

texts:
Our SQL Server is experiencing sudden high CPU usage since 12:30am today. This server is used internally so nobody should be accessing the server at that time.

I'm not sure why 12 hours later the CPU usage is still at 100%. Looking at the resource monitor on the server it is sqlservr.exe that is using 99% of the CPU.

We have re-index jobs that kick off at 12:30am every other morning, but those jobs were completed. These jobs also update the statistics via DBCC DBREINDEX.

I've been researching for the past 3 hours, but most of the answers have been on tuning queries or troubleshooting a single query that is hogging the CPU. ALL of our queries have been slow today, which is very abnormal.. I've checked multiple times for a single query, but haven't found anything.

I've run an sp_who2 'Active', but there are no processes in the suspended state.

I have tried several different DMV and other queries to pinpoint the issue, but haven't been able to find anything..

It seems like SQL Server is hung up and needs a restart, but I'd like to avoid that at all costs...


texts:
I build a new website for a client (WordPress is underlying) and have access trough FTP to his webspace. He does not provide or even know more informations than this because he had taken it from an former employee.

Is it possible to get know if any MySQL database exist on localhost and connect to it without the knowledge of an username or password?

I dont think that the web space host could help me with this problem, because they switched there business orientation a while ago and don't provide any new web space at all.


texts: Efficient way to store multi choice in a column with few options

texts:
I am looking for advices on how to handle multiple choices in a column?

By way of example lets say selecting a fruit for lunch.  Options are Apple or Pear.  And you are 100% sure those two options will always be available, but you can only be 95% sure that other fruit won't be added to the list, but can be sure that if they are there will never be more than 3 or 4 types ever, and once added they will never be removed.

Then let's say you have another similar column for sandwich types, and another for drink types with the same requirements for their types.

Is it still best to create a table for fruits, another for sandwiches and another for drinks?

What about if there really will only ever be 2 options, like new car or second hand car but you want to use the descriptions, not a boolean value so other staff can drag and drop a quick query in MS Access or paste it into a spreadsheet?

If it matters the DB is ms SLQ Server, and there will be about 1500-2000 new rows per month in this table, but queries sometimes going back over several years of data.  Currently have about 5 years of data to insert into this table when complete.


texts:
Imagine having a tun of hashes in a Redis Database, each representing a user object. How would you get the user with for example the lowest registration date? The user where ts_registered is the lowest. I searched a bit on the Commands Page on redis.io but didn't seem to find a command for it there.

I don't have much experience with Redis and I'm basically just checking out how it works.


texts: MySQL Dump Restoration when FOREIGN_KEY_CHECKS=0

texts:
I currently use a SaaS DBMS. Backups of my database are provided to me in the form of a MySQL dump, and I noticed that FOREIGN_KEY_CHECKS=0. I am annoyed by this because it appears that this dump is not actually conducive to a restoration of the database(including relationships) on my end should the need arise.

Upon review of the structure I see that there are no Foreign Keys in the tables, but I see there are KEYS(indexes) that appear to correspond with what the foreign keys should be.

When performing a MySQL dump, does setting FOREIGN_KEY_CHECKS=0 mean that there are no relationships/foreign keys included in the dump?

Is it true that when restoring a MySQL dump that had FOREIGN_KEY_CHECKS=0 at the time of the dump , the foreign key relationships will not be present?

Is there a way to create foreign key relationships from the existing KEYS?


texts: SQL 2012 Autogrow and file extension problem

texts:
I had my data and log files set to auto grow by 10%.
This has caused the transaction logfile to grow to an astronomical 600 GB in size and led to the error

Autogrow of file PFMultilinguaklPOO_log in database
PFMultilingual took 523826 milliseconds.
Consider using ALTER Database to set a smaller FILEGROWTH for this file

This was causing DB timeouts.

I changed the auto growth setting from 10% to a fixed size to rectify this in the short term but I obviously need to shrink the size of my log file.
However when I went to investigate the best method of doing this I noticed a peculiar issue with the file extensions.

My data file has the .LDF extension and my log file has the .MDF extension which is obviously the wrong way round. I'm not sure how this happened. Can the extensions be changed back without any conflict?
I am attaching an image to show this.

I was wondering if anyone has seen this before and what the best way to resolve it is.


texts: When trigger returns NULL, row is not inserted, but result of the operation is still reported as successful (INSERT 0 0). And in my opinion this is not logical, as one would probably want to handle this properly. I wonder if there's a way to throw an actual error from the trigger?


texts:
I need help in query optimization,I am having table with structure

Create Table: CREATE TABLE `ip_country_mapping` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `cron` bigint(20) NOT NULL,
  `start_ip_number` bigint(20) NOT NULL,
  `end_ip_number` bigint(20) NOT NULL,
  `country` varchar(2) COLLATE utf8_bin NOT NULL,
  `state` varchar(30) COLLATE utf8_bin DEFAULT NULL,
  `city` varchar(30) COLLATE utf8_bin DEFAULT NULL,
  `zip` varchar(30) COLLATE utf8_bin DEFAULT NULL,
  `creation_date` datetime NOT NULL,
  `last_updation_date` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_IP_COUNTRY_MAPPING_TEMP_START_IP_NUMBER` (`start_ip_number`),
  UNIQUE KEY `UK_IP_COUNTRY_MAPPING_TEMP_END_IP_NUMBER` (`end_ip_number`),
  KEY `FK_IP_COUNTRY_MAPPING_TEMP_CRON` (`cron`),
  KEY `ind_ipscan` (`end_ip_number`,`start_ip_number`),
  CONSTRAINT `FK_IP_COUNTRY_MAPPING_TEMP_CRON` FOREIGN KEY (`cron`) REFERENCES `cron` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2020168 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

The query to optimized is

SELECT ipcountrym0_.country as col_0_0_, ipcountrym0_.state as col_1_0_,ipcountrym0_.city as col_2_0_
FROM ip_country_mapping ipcountrym0_
WHERE ipcountrym0_.start_ip_number<=1376791568
AND ipcountrym0_.end_ip_number>=1376791568;

EXPLAIN plan is giving the output as

EXPLAIN SELECT ipcountrym0_.country as col_0_0_, ipcountrym0_.state as col_1_0_, ipcountrym0_.city as col_2_0_
FROM ip_country_mapping ipcountrym0_
WHERE ipcountrym0_.start_ip_number<=1376791568
AND ipcountrym0_.end_ip_number>=1376791568;
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: ipcountrym0_
             type: ALL
    possible_keys: UK_IP_COUNTRY_MAPPING_TEMP_START_IP_NUMBER,UK_IP_COUNTRY_MAPPING_TEMP_END_IP_NUMBER,ind_ipscan
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 2081584
            Extra: Using where

If i add limit 1 to the above query,it scans 199999 rows.The output generated by this query is only one row.if i put order by clause in the above query with limit 1 then it scans only 2 rows.

EXPLAIN SELECT ipcountrym0_.country as col_0_0_, ipcountrym0_.state as col_1_0_, ipcountrym0_.city as col_2_0_
FROM ip_country_mapping ipcountrym0_
WHERE ipcountrym0_.start_ip_number<=1376791568
AND countrym0_.end_ip_number>=1376791568
ORDER BY id LIMIT 1;
        *************************** 1. row ***************************
                   id: 1
          select_type: SIMPLE
                table: ipcountrym0_
                 type: index
        possible_keys: UK_IP_COUNTRY_MAPPING_TEMP_START_IP_NUMBER,UK_IP_COUNTRY_MAPPING_TEMP_END_IP_NUMBER,ind_ipscan
                  key: PRIMARY
              key_len: 8
                  ref: NULL
                 rows: 2
                Extra: Using where
        1 row in set (0.00 sec)

Is this the correct way my query is optimizing or there is some thing wrong the way i had tried to optimize.
My Manager is are not agreeing  with this optimization as he consider that the optimizer is first scanning the index column id and then the start ip number and end ip number which is not relevant.

Can somebody please explain how optimizer is working here and is this correct way the optimizing the query .It is argued that optimizer is showing wrong plan.


texts: What are the options of reducing tempdb latency?

texts:
Took the job of monitoring and tununing a SQL Servers 2012 Enterprise Ed. SP1 (64 bit) on Windows Server 2008 R2 Standard Ed.

All of them are running inside virtual machines. One SQL Server per a regional office on a  single RAID10 or RAID5. In other words, I do not have possibility to configure an SQL Server using different physical units (hard drives).

I observe an elevated reading and writing latency on tempdb system database.
What are my options in reducing this latency of tempdb database?


texts: Method or Counter to track dropped connections in SQL Server?

texts:
Our production server is running SQL Server 2008 (non r2).

I'm working on investigating and issue that appears to be networking or connection related, where our web server is losing or dropping connections to the database server.

It would assist me greatly to have a method or counter that I can use to track DROPPED connections to the SQL Server. Specifically, we want to know if the ODBC/ADO connection was forcibly closed by the CLIENT.

Conceivably we could also track when a query was cancelled by the client, which may give us the same information.

From my own research I don't see a way to track this with perfmon counters, but I'm not that familiar with them.


texts: Retrieve one of two possible values from 1 table in INFORMIX

texts:
I have a table which for the same key, it will always have one record with value 'P' but sometimes that same key could have two records one with value 'P' and one with value 'L'; I need to create a VIEW that if both 'P' and'L' exist; it will return the record with 'L', ELSE, it will return record with value 'P';

ex1 has two records 'P' and 'L' -> only 'L' should be returned
ex2 has one record 'P' -> 'P' will be returned.


texts:
I get an error when I try to create a table:

  ERROR 1005 (HY000): Can't create table 'phact_greenland.cache' (errno: 121)

CREATE TABLE IF NOT EXISTS `cache`
(
    `cid` varchar(255) NOT NULL DEFAULT ''
        COMMENT 'Primary Key: Unique cache ID.'
    , `data` longblob
        COMMENT 'A collection of data to cache.'
    , `expire` int(11) NOT NULL DEFAULT '0'
        COMMENT 'A Unix timestamp indicating when the cache entry
                should expire, or 0 for never.'
    , `created` int(11) NOT NULL DEFAULT '0'
        COMMENT 'A Unix timestamp indicating when the cache entry was created.'
    , `serialized` smallint(6) NOT NULL DEFAULT '0'
        COMMENT 'A flag to indicate whether content is serialized (1) or not (0).'
    , PRIMARY KEY (`cid`)
    , KEY `expire` (`expire`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
    COMMENT='Generic cache table for caching things not separated out...';

How do i solve it ?


texts:
I am using TimescaleDB (an extension to PostgreSQL) to store altitude data from a GPS sensor. The table looks like:

CREATE TABLE IF NOT EXISTS altitude
(
  time              TIMESTAMPTZ      NOT NULL,
  workout_id        VARCHAR(24)      NOT NULL,
  user_id           VARCHAR(24)      NOT NULL,
  altitude          DOUBLE PRECISION NOT NULL,
  gain              DOUBLE PRECISION NOT NULL,
  loss              DOUBLE PRECISION NOT NULL,
  activity_type     VARCHAR(32)      NOT NULL,
  elapsed_distance  DOUBLE PRECISION NOT NULL,
  absolute_distance DOUBLE PRECISION NOT NULL,
  elapsed_time      DOUBLE PRECISION NOT NULL,
  absolute_time     DOUBLE PRECISION NOT NULL
);

altitude This is the geographical altitude at the point the altitude point was recorded.
absolute_time This is the absolute time since the start of the recording
absolute_distance This is the absolute distance traveled since the start of the recording
elapsed_distance This is the elapsed distance between two successive altitude points
elapsed_time This is the elapsed time between two successive altitude points
gain The altitude gain between two successive altitude points
loss The altitude loss between two successive altitude points

I want to calculate the speed by distance, that is I want something like:

SELECT distance, speed FROM calculate_speed_by_distance(from_time, to_time, step_distance) ORDER BY distance

The step should specify the distance interval the resulting speed should be calculated beginning at the distance already traveled at from_time to the distance traveled at to_time.
E.g. speed=200 (with a distance at from_time of 0m and a distance of to_time of 756m) sould mean:

0m     200m     400m     600m     800m
0km/h  5km/h    10km/h   11km/h   8km/h

The speed (absolute_distance/absolute_time) should be averaged among all successive altitude points in each interval.

If there are no altitude points in some intervals, the last calculated speed point can be used.

So I need something like the following:

CREATE OR REPLACE FUNCTION calculate_speed_by_distance(arg_time_from timestamp,
                                                 arg_time_to timestamp,
                                                 arg_distance_step bigint,
                                                 arg_workout_id varchar(24),
                                                 arg_user_id varchar(24))
    RETURNS TABLE
            (
                distance double precision,
                value    double precision
            )
AS
'
    BEGIN
        RETURN query with ...
    END;
' LANGUAGE PLPGSQL;

So the question is: How to aggregate the data in the current format to calculate the speed by distance in the aforementioned way?

EDIT:

Example data for altitude points: See here

Best,

Philipp


texts: Why is READ COMMITTED a common default transaction isolation level?

texts:
I am wondering if anyone knows the history of why READ COMMITTED is the default transaction isolation level for PostgreSQL, SQL Server, Oracle, Vertica, DB2, Informix, and Sybase.

MySQL uses default REPEATABLE READ, at least with InnoDB, as do SQLite and NuoDB (they call it "Consistent Read").

Again, I am not asking for what the differences are between different isolation levels, but rather for some explanation of why the default was chosen to be READ COMMITTED in so many SQL databases. My wild guesses are: small performance benefit, ease of implementation, some recommendation in the SQL standard itself, and/or "that's the way it's always been". The obvious downside of this choice is that READ COMMITTED tends to be quite counterintuitive for developers and can lead to subtle bugs.


texts: Mysterious postgres process pegging CPU at 100%; no running queries

texts:
I have a recurring issue where a postgres process is pegging my cpu at > 100%. It's a strange one, you can see below the command is ./Ac2p20853 (surrounded by asterisks, added by me).

Running SELECT pid, datname, usename, query FROM pg_stat_activity shows nothing too interesting (to me at least). Simply a few inserts into pg_proc.

How would you go about debugging what is the issue here?

See below for output from ps, grepping postgres.

Thank you.

deploy@localhost:~$ ps auxww | grep ^postgres
postgres   590  0.0  0.3 309312 13768 ?        Ss   20:29   0:00 postgres: deploy template1 5.5.5.5(53394) idle
postgres  2264  0.0  0.3 309312 13748 ?        Ss   20:20   0:00 postgres: deploy template1 5.5.5.5(37818) idle
postgres  3079  0.0  0.3 309312 13776 ?        Ss   20:12   0:00 postgres: deploy template1 5.5.5.5(60006) idle
postgres  3809  0.8  0.0   4504  1656 ?        S    18:11   1:19 /bin/sh ./systemd
**postgres  3862  167  0.1 252512  5464 ?        Ssl  18:11 265:06 ./Ac2p20853**
postgres  5287  0.0  0.6 308388 24560 ?        S    Aug09   1:22 /usr/lib/postgresql/9.6/bin/postgres -D /var/lib/postgresql/9.6/main -c config_file=/etc/postgresql/9.6/main/postgresql.conf
postgres  5289  0.0  2.1 308516 87096 ?        Ss   Aug09   0:01 postgres: checkpointer process
postgres  5290  0.0  0.5 308388 22832 ?        Ss   Aug09   0:04 postgres: writer process
postgres  5291  0.0  0.2 308388  8676 ?        Ss   Aug09   0:03 postgres: wal writer process
postgres  5292  0.0  0.2 309196 11972 ?        Ss   Aug09   1:03 postgres: autovacuum launcher process
postgres  5293  0.0  0.1 163724  5004 ?        Ss   Aug09   2:34 postgres: stats collector process
postgres  6065  0.0  0.3 309312 13824 ?        Ss   20:04   0:00 postgres: deploy template1 5.5.5.5(51616) idle
postgres  7384  0.0  0.3 309312 13832 ?        Ss   20:40   0:00 postgres: deploy template1 5.5.5.5(55916) idle
postgres 11467  0.1  4.0 328788 161612 ?       Ss   Aug09  10:50 postgres: deploy db_production [local] idle
postgres 11638  0.0  4.3 339336 174368 ?       Ss   Aug09   4:52 postgres: deploy db_production [local] idle
postgres 11812  0.0  0.5 316092 21496 ?        Ss   Aug09   0:00 postgres: deploy postgres [local] idle
postgres 12707  0.0  0.4 315660 16820 ?        Ss   Aug09   0:00 postgres: deploy db_production [local] idle
postgres 17511  0.0  0.3 309312 13716 ?        Ss   20:33   0:00 postgres: deploy template1 5.5.5.5(33460) idle
postgres 17968  0.0  0.3 309312 13804 ?        Ss   20:44   0:00 postgres: deploy template1 5.5.5.5(34548) idle
postgres 18974  0.0  0.3 309312 13768 ?        Ss   20:16   0:00 postgres: deploy template1 5.5.5.5(52290) idle
postgres 19017  0.0  0.3 309312 13752 ?        Ss   20:24   0:00 postgres: deploy template1 5.5.5.5(37348) idle
postgres 21120  0.0  0.3 309312 13788 ?        Ss   20:08   0:00 postgres: deploy template1 5.5.5.5(33084) idle
postgres 24189  0.0  0.3 309312 13744 ?        Ss   20:36   0:00 postgres: deploy template1 5.5.5.5(35660) idle
postgres 31498  0.0  0.3 309312 13804 ?        Ss   20:48   0:00 postgres: deploy template1 5.5.5.5(39618) idle


texts: Restore Error: Could not upgrade the metadata

texts:
I am trying to restore a database from a Prod environment (11.0.6020.0) to a lower environment server (11.0.3128.0).  I realize the lower has an older SP, we are working on correcting that now.  The restore should still work though, it has for months, but I'm getting the following error message.  We just recently enabled this database for CDC, I'm not specifying keep_cdc in the restore.  Anybody seen this error before and know how to resolve it?

  Msg 22841, Level 16, State 1, Procedure sp_cdc_vupgrade, Line 320
  [Batch Start Line 0] Could not upgrade the metadata for database
  'DBName' that is enabled for Change Data Capture. The failure occurred
  when executing the action 'alter cdc.change_tables index
  change_tables_unique_idx with (drop_existing = on)'. The error
  returned was 4922: 'line 102, state 9, ALTER TABLE ALTER COLUMN
  column_id failed because one or more objects access this column.'. Use
  the action and error to determine the cause of the failure and
  resubmit the request.

RESTORE DATABASE [DBName]
FROM  DISK = @FileToRestore WITH
MOVE  'DBName' to 'W:\Data\DBName\DBName.mdf',
MOVE  'AmexDB_log' to 'W:\Logs\DBName\DBName_Log.ldf',
NOUNLOAD, REPLACE , FILE = 1, STATS = 10
GO


texts: Connect excel to ms-access front end database

texts:
I have an access database split. It is part of an app working along with an excel add-in.

Question:

Within excel, should I address all query updates directly to the back-end version (in the shared network)?

Or should I connect to the front-end (local)?

As I understand splitting access prevents from data corruption in shared database (when users open same file). If I use a SQL command within excel and send it directly to the back-end, then I am using excel as the front-end (and that clears the issue, am I right?)

Many thanks in advance.


texts: Compare two columns for difference mark the difference in mysql

texts:
I need to compare two fields and mark the similar fields in third table.

For Example:

Tables

customer:
  phone id,
  phonenumber
  followstatus

received_Customer:
  rc_id,
  rc_phonenumber.

I need to mark the fdnumber with f or n/a in follow up which is in received and customer table.

update customer
select b.phonenumber
from received_customer n
join customer b
on b.phonenumber = n.phonenumber
SET followUpStatus= 'y' ;

I run query in php.