texts:
I'm totally new to database tuning, and even though I've done well so far, I'm stuck with my database cache query efficiency 0%. Any help with this would much appreciated

I've tried increasing query_cache_limit and table_open_cache but currently they're really high as server only has 1GB RAM. No matter how high I set these, mysqltuner always comes with same recommendations of raising them even more. I don't know if it's connected

I'm using mariaDb and this is the result I get with mysqltuner

[OK] Logged in using credentials from debian maintenance account.
[!!] Currently running unsupported MySQL version 10.1.0-MariaDB-1~trusty-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MRG_MyISAM
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
[--] Data in InnoDB tables: 17M (Tables: 339)
[--] Data in MEMORY tables: 0B (Tables: 16)
[--] Data in MyISAM tables: 684K (Tables: 14)
[OK] Total fragmented tables: 0

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 4s (138 q [34.500 qps], 51 conn, TX: 29K, RX: 14K)
[--] Reads / Writes: 100% / 0%
[--] Total buffers: 600.0M global + 5.4M per thread (40 max threads)
[OK] Maximum possible memory usage: 816.2M (82% of installed RAM)
[OK] Slow queries: 0% (0/138)
[OK] Highest usage of available connections: 2% (1/40)
[OK] Key buffer size / total MyISAM indexes: 64.0M/407.0K
[!!] Query cache efficiency: 0.0% (0 cached / 87 selects)
[OK] Query cache prunes per day: 0
[OK] Temporary tables created on disk: 11% (10 on disk / 84 total)
[OK] Thread cache hit rate: 98% (1 created / 51 connections)
[!!] Table cache hit rate: 19% (92 open / 480 opened)
[OK] Open file limit used: 0% (84/20K)
[OK] Table locks acquired immediately: 100% (113 immediate / 113 locks)
[OK] InnoDB buffer pool / data size: 256.0M/17.9M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    MySQL started within last 24 hours - recommendations may be inaccurate
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
Variables to adjust:
    query_cache_limit (> 532M, or use smaller result sets)
    table_open_cache (> 10000)

And this is my.cnf file

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc_messages_dir = /usr/share/mysql
lc_messages = en_US
skip-external-locking

bind-address        = 127.0.0.1

max_connections     = 40
connect_timeout     = 5
wait_timeout        = 600
max_allowed_packet  = 8M
thread_cache_size       = 128
sort_buffer_size    = 2M
bulk_insert_buffer_size = 8M
tmp_table_size      = 8M
max_heap_table_size = 8M

myisam_recover          = BACKUP
key_buffer_size     = 64M
#open-files-limit   = 2000
table_open_cache    = 10000
myisam_sort_buffer_size = 512M
concurrent_insert   = 2
read_buffer_size    = 2M
read_rnd_buffer_size    = 1M

query_cache_limit       = 532M
query_cache_size        = 256M
query_cache_type = 1
query_cache_strip_comments =1

default_storage_engine  = InnoDB
innodb_buffer_pool_size = 256M
innodb_log_buffer_size  = 8M
innodb_file_per_table   = 1
innodb_open_files   = 400
innodb_io_capacity  = 400
innodb_flush_method = O_DIRECT

[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer      = 16M


texts:
I have table B and I have Table A.  Table B contains records with dates I need to pull. However, I only want to pull them if:

They do not exist at all in table A
They exist in table A, but only when table A.status = 0

The trick is I have no key to just join the tables with. So, I am using the Item column and the date column to join. (Notice two items can have the same date. An there can also be multiple of the same items, but with different dates as well.)

So table B might have:

    [Item]      [Qty]   [Date]
    F-001100,   400      7/14/2016
    F-002200,   750      7/28/2016
    F-000767,   1000     7/28/2016
    F-006006,   300      8/01/2016
    F-002200,   900      7/30/2016

Table A might have:

[Item]    [Status]  [Qty]    [Date0]     [Date2]     [Date3]     [Date4]
F-001100, 1,        400      7/14/2016   7/15/2016   7/16/2016   7/17/2016
F-002200, 0,        750      7/28/2016   7/29/2016   7/30/2016   7/31/2016

So the result set should be:

[Item]       [Qty]     [Date]
F-002200,    750       7/28/2016
F-000767,    1000      7/28/2016
F-006006,    300       8/01/2016
F-002200,    900       7/30/2016   <--- I can't get this to show up!!

The matching works like this:

WHERE A.[Item] = B.[Item]
AND (   A.[Date1] = B.[Date]
     OR A.[Date2] = B.[Date]
     OR A.[Date3] = B.[Date]
     OR A.[Date4] = B.[Date] )


texts: How to connect Microsoft SQL Database with Active Directory Password Authentication (AD Login)

texts:
I have web application and I wanted to use Active Directory Password Authentication for Database login.

I already setup user login for Active Directory, but it show the error like below.

Help me! please...


texts: How to create a job that uses Powershell script?

texts:
I have a powershell script which deletes old backups from NAS storage. I have already tested it using Windows Powershell and it works but I couldn't perform the same thing in SQL Server. I have created a cmdExec type job and then wrote the script below (script is on desktop):

"C:\Windows\system32\WindowsPowerShell\v1.0\powershell.exe"

"C:\Users\domainAccount\Desktop\"

".\deleteold.ps1 -FolderPath \\192.168.x.x\Backups -FileAge 2 -ListOnly -LogFile \\192.168.x.x\Backups\log.log"

But I do not get any result as when I run the job it just keeps running infinitely until I stop that job.


texts: How to populate additional rows after some calculation or addition

texts:
I have following table,

Table's Weight entries goes up to 100, now I need to increase this to 10000. I am able to make a pseduo code of what I want but I am not sure how to do it in T-SQL,

Loop and add 100 to weight and cost of 100 which is not visible in above table but let's assume it's 600.

If weight is more then 100, then following formula will be used,

number of 100's in weight x equilent cost

E.g. if weight is 350 then calculations will be,

Final Cost = (3 x 600) + (50 x 400)

-- 600 is cost of 100 weight
-- 400 is cost of 50 weight

I am only assuming weights here but in script I want it to read directly from table. Is there any way to achieve this easily ? or i will need to create an app ?

insert into table1
(
    Weight ,
    Cost ,
    CategoryId
)
select
    Weight + 100,
    Cost + (select Cost from table1 where weight = 100 and CategoryId = of respective row ????),
    CategoryId
from
    #temp_table1

not sure how to complete this


texts: full-text search 7608 (0x80004005) error message debug

texts:
Someone tries to run the following line :
exec sp_fulltext_column 'tbElementFullTextMvCustomProperty5', 'EMVCP_E_NAME', 'add', 0, NULL

and then receives the following error :
(1 row(s) affected)

(1 row(s) affected)

Server: Msg 7608, Level 17, State 1, Procedure sp_fulltext_column, Line 138
An unknown full-text failure (80040d23) occurred in function EnumCatalogs on full-text catalog 'catElementFullTextMvCustomProperty5'.

I have searched online and thought that maybe the full-text search is not enabled on this database.
According to the part "How to set up full-text search on a database" of this note, it would seem that you can run the following procedures to enable full text search :
USE <DB_NAME>
GO
EXEC sp_fulltext_database 'enable'
GO
EXEC sp_fulltext_catalog 'FTC_titles', 'create'
GO
EXEC sp_fulltext_table titles, 'create', 'FTC_titles', UPKCL_titleidind
GO
EXEC sp_fulltext_column titles, title, 'add'
GO
EXEC sp_fulltext_table titles, 'activate'
GO
EXEC sp_fulltext_catalog 'FTC_titles', 'start_full'
GO

This query ran for 20 minutes so I cancelled it, I guessed it did not work. Did I do wrong ? Should I had kept it going ? What should be my next debugging step ?
This is a windows server 2003 running a sqlserver 2000 database
Thanks


texts: What are the Pros and Cons of assigning value for ids from an offset? (Ex: 100,200,1000)

texts:
Pre-dominantly, everybody uses the int data-type or it's variants tinyint,smallint and mediumint to generate a unique value for the id field of their database table(s).

I have seen so many questions, asking How to start auto-increment from a value (offset) ? When it comes to generating ids for orders table in specific, I feel everybody wants to start off from an offset.

So, in this context, my questions are:

Is it more of a business rule? If so, how does a business rule make sense while generating numbers?
If it's an individual's decision then what could be the possible elements  to be considered while deciding to assign id's value from an offset??
Why not start from 1 for all the ids and what is the need to start assigning ids from an offset? What problem does it solve? What are the Pros and Cons of doing so?


texts: How to do Pagination with UUID v4 and Created Time on Concurrent Inserted Data?

texts:
Context:

Out of curiosity, I'm doing load testing for my application. And then the result there's a lot of concurrent inserts happened.

After doing the load testing on create-endpoint, I'm trying to do load testing on the Fetch endpoint, including testing the pagination. For the pagination, I'm combining two columns, id (PK with UUID v4) and created_time. Also, I've added an index for faster sorting.
I'm following these solutions from here.

https://stackoverflow.com/a/58943917/4075313
https://dba.stackexchange.com/a/205385/117466

Problem:

Since the data was inserted concurrently, there are a few rows that have the same created_time, in my case up to 100(rows) in the same timestamp.

This is my table schema, an example

BEGIN;

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

DROP TABLE IF EXISTS "payment_with_uuid";

CREATE TABLE "payment_with_uuid" (
 id VARCHAR(255) PRIMARY KEY NOT NULL DEFAULT (uuid_generate_v4()),
 amount integer NULL,
 name varchar(255) default NULL,
 created_time TIMESTAMPTZ NOT NULL DEFAULT (now() AT TIME ZONE 'utc')
);

CREATE INDEX idx_payment_pagination ON payment_with_uuid (created_time, id);

COMMIT;

This is my query,

SELECT  * from payment_with_uuid ORDER BY  created_time DESC, id DESC LIMIT 10;

It will return 10 rows of payment, assume the data will look like this, and assume the timestamp is same until the 100th row

+-------------------------------------+--------+------------+---------------------+
| id                                  | amount | name       | created_time        |
+-------------------------------------+--------+------------+---------------------+
| ffffa567-e95a-4c8b-826c-e2be6acaeb6d| 32003  | Allistair  | 2020-05-24 21:27:10 |
| ffff2dd6-3872-4acc-afec-7a568935f729| 32003  | James      | 2020-05-24 21:27:10 |
| fffe3477-1710-45c4-b554-b539a9ee8fa7| 32003  | Kane       | 2020-05-24 21:27:10 |

And for fetching the next page, this is my query looks like,

SELECT * FROM payment_with_uuid
WHERE
created_time <= '2020-05-24 21:27:10' :: timestamp
AND
id <'fffe3477-1710-45c4-b554-b539a9ee8fa7'
ORDER BY created_time DESC, id DESC LIMIT 10;

And because of that, the pagination messed up, like some records that exist on the 1st page, may exist on 2nd, or 3rd, or any pages. And sometimes the records are missing.

Questions and Notes:

Is there any way to do this in a more elegant way?
I know using auto-increment will solve this, but choosing auto-increment id is not an option for us, because we're trying to make everything is consistent across microservice, many services already using UUID as the PK.
Using offset and limit will also solve this, but it's not a good practice as far as I know as this article explained https://use-the-index-luke.com/no-offset
I'm using Postgres 11.4


texts: SQL Server Log Shipping: Copy copies 0 files

texts:
(This is SQL 2014, but applies to several recent versions)

I had the SQL Server log shipping wizard create the script for the primary and secondary. Since the primary already backs up logs every few minutes, we did not run the script on the primary.

We ran it on 2ndary, and set up all the proxy accounts, etc, so that files can be copied.

The jobs run. Issue is that the copy job... never copies any files... even though there are files there.

We confirmed that when we run a cmd prompt as the same user (the proxy user account) that we can see the needed files on the remote location (to copy to the local location).

Message
2015-02-11 01:50:08.00  Checking to see if any previously copied log backup files that are required by the restore operation are missing. Secondary ID: '72ac3286-f3cb-4f80-a8a4-368e3b38e502'
2015-02-11 01:50:08.00  The copy operation was successful. Secondary ID: '72ac3286-f3cb-4f80-a8a4-368e3b38e502', Number of log backup files copied: 0
2015-02-11 01:50:08.01  ----- END OF TRANSACTION LOG COPY     -----

How do we kick the secondary so it starts copying files?


texts: Partitioning increases CPU usage in mariadb

texts:
I'm using mariadb 10.2, and I had a table with a structure similar to the one below, currently with 15gb, where I insert a few thousand records per minute all the time, each batch for a specific account (AccountId1).

AccountId1 bigint PK,
AccountId2 bigint PK,
ActionType int,
ActionDate timestamp

Looking at the process list, multiple inserts were waiting in line for this table, and they were always one insert running at a time, so I decided to partition this table by hash on the AccountId1, and created 32 partitions.

The inserts now jumped from 1 or 2 a second to 8 during peak times, and that worked great. The issue is that the CPU usage jumped from 15% to 40% after the change, and even stopping the insert process completely for a few hours, the CPU won't go down.

There are a few places where we select from this table that could span all partitions, but these are processes that run every few hours and don't take that long. Other frequent queries always use the AccountId1 field and are indeed querying a single partition by looking at the query plan.

Is there any overhead associated with partitioning, even if no sql is touching that table? What could be the reason for this increase in CPU?

Note: there are around 2000 concurrent connections at any time, but the # of connections before and after the partitioning were the same.


texts: AlwaysOn Availability Groups Dev, QA, and Staging Environments

texts:
Our company would like to implement Always On Availability Groups. The Final PRODUCTION Plan is for Primary Replica in one state, and Secondary Replica in another state location. The secondary replica will be Read-only Asynchronous.

When creating the Devops Previous Instances, do you typically setup Alwayon Availability Groups in the  Dev, QA, or Staging Environment. So Should I setup another SQL Server Instance/Availability Database for any environment? I believe our Test Staging deployments should always be setup to closely be mirroring Production, and at the very Least, I should setup AlwaysOn for Staging Env (we can skip Dev and QA if required). Financial and  getting resources approved is always a balance in any company.

By the way, the QA or Staging Secondary Replica data would transfer from the QA or Staging Primary Data Replica

Thanks,


texts: How to create a new user in a remote offline database without elevating roles

texts:
An enterprise C++ system that needs to be "translated", uses ODBC for connecting to SQL Server Database.

The system has one central main database, many distributed local databases, which are actually offline and are periodically kept updated with the central database by exchanging merging xml files, something like a star topology. The distributed databases are stored on many different OS and different local domains.

Each user has been registered in the SQL Server and with certain user roles on the database. The number of login users at the database were kept fixed.

My question is how the remote new user to create/update his own profile and/or credentials without the need to elevate his rights.


texts: SSMS18 - Default File Location (Changed ssms.Application.pkgdef)

texts: I have changed the DefaultProjectsLocation (SSMS18) from the default (My Documents) to a folder on my network. But when I run SSMS18 and open Projects it is still looking at the default location. What am I missing?


texts: Efficiently storing sets of key-value pairs with wildly different keys

texts:
I inherited an application that associates many different types of activities with a site.  There are roughly 100 different activity types, and each one has differing set of 3-10 fields.  However, all activities have at least one date field (could be any combination of date, start date, end date, scheduled start date, etc.), and one responsible person field.  All other fields vary widely and a start date field will not necessarily be called "Start Date".

Making one subtype table for each activity type would result in a schema with 100 different subtype tables, which would be too unwieldly to deal with.  The current solution to this problem is to store the activity values as key-value pairs.  This is a greatly simplified schema of the current system to get the point across.

Each Activity has multiple ActivityFields; each Site has multiple Activities, and the SiteActivityData table stores the KVPs for each SiteActivity.

This makes the (web-based) application very easy to code because all you really need to do is loop over the records in SiteActivityData for a given activity and add a label and input control for each row to a form.  But there are lots of problems:

Integrity is bad; it's possible to put a field in SiteActivityData that does not belong to the activity type, and DataValue is a varchar field so numbers and dates need to be constantly cast.
Reporting and ad-hoc querying of this data is difficult, error prone, and slow.  For example, getting a list of all activities of a certain type that have an End Date within a specified range requires pivots and casting varchars to dates.  The report writers HATE this schema, and I don't blame them.

So what I am looking for is a way to store a large number of activities that have almost no fields in common in a way that makes reporting easier.  What I have come up with so far is to use XML to store the activity data in a pseudo-noSQL format:

The Activity table would contain the XSD for each activity, eliminating the need for the ActivityField table.  SiteActivity would contain the key-value XML so each activity for a site would now be in a single row.

An activity would look something like this (but I haven't fleshed it out fully):

<SomeActivityType>
  <SomeDateField type="StartDate">2000-01-01</SomeDateField>
  <AnotherDateField type="EndDate">2011-01-01</AnotherDateField>
  <EmployeeId type="ResponsiblePerson">1234</EmployeeId>
  <SomeTextField>blah blah</SomeTextField>
  ...

Advantages:

The XSD would validate the XML, catching errors like putting a string in a number field at the database level, something that was impossible with the old schema that stored everything in varchar.
The recordset of KVPs that is used to build the web forms could easily be reproduced using select ... from ActivityXML.nodes('/SomeActivityType/*') as T(r)
An xpath subquery of the XML could be used to produce a result set that has columns for start date, end date, etc without using a pivot, something like select ActivityXML.value('.[@type=StartDate]', 'datetime') as StartDate, ActivityXML.value('.[@type=EndDate]', 'datetime') as EndDate from SiteActivity where...

Does this seem like a good idea?  I can't think of other ways to store such a large number of differing sets of properties.  Another thought I had was keep the existing schema and translate it into something more easily queryable in a data warehouse, but I have never designed a star schema before and would have no idea where to begin.

Additional question: If I define a tag as having a date data type in the XSD using xs:date, is SQL Server going to index it as a date value?  I'm concerned if I query by date it will need to cast the date string to a date value and blow any chance of using an index.


texts: Alter disabled trigger in SQL Server without enabling it

texts:
It seems like triggers get enabled automatically after altering them. How can I alter triggers programmatically without changing their enabled/disabled status?

Code to reproduce:

create trigger trig
on MyTable
after insert
as select 0

go

disable trigger trig on MyTable

go

select is_disabled from sys.triggers where name = 'trig'

go

alter trigger trig
on MyTable
after insert
as select 1

go

select is_disabled from sys.triggers where name = 'trig'


texts: Was there any query to retrieve current timezone setting for the SQL server with respect to some offset like UTC..


texts:
So let me first start the question with my understanding of how MongoDb stores data on disk: So when you create a database in mongodb, it allocates a large file named <databasename>.0 and within that file allocates extents that are contiguous regions that correspond to data for a particular collection or particular index.

At such point as this datafile is filled, it creates a new file called <databasename>.1 and populates it in a similar way. Therefore it seems sensible to assume that the most recently inserted data into a particular database will be in the highest numbered file (and my performance tests confirm this).

However, I can't see how this could be true for indices... since we're talking about a bTree, it doesn't seem possible/sensible to have this bTree scattered across files in the same way. As Mongo is doing the maintenance for an index, does the whole index live in one extent until it outgrows it at which point it is relocated to the current (highest numbered datafile)?.

This has become important to me as when starting a database from Amazon EBS snapshot, it seems there's a huge overhead for hitting these datafiles until the volume warms up. I am only interested in a subset of the most recent N documents from a collection. If I could be sure I was only going to need the most recent couple of datafiles I could prewarm these files by reading sequentially before starting mongod.


texts: Getting Ranked Position - SQL - Simple Query

texts:
I have a very simple database structure and am struggling to write a command to find the ranked position of the user.

------------------
id     |  average
------------------
1      |  79
------------------
2      |  99
------------------
3      |  22

How would I be able to find the ranked position of the user with id 1? #2 would be ranked 1st. I want the rank to be descending with the highest number in 1st place.


texts: Is it required/recommended to run the Postgresql server process with the `postgres` OS user?

texts:
If I were to run 2 or 3 postgres OS processes (one physical machine, multiple postgres -D ... processes), would it be more secure to run them under different OS users (ie other than the postgres user)?

Or is there a specific reason to run them all under the postgres user?


texts: Get partial match from GIN indexed TSVECTOR column

texts:
I would like to get results by query this:

SELECT * FROM (
  SELECT id, subject
  FROM mailboxes
  WHERE tsv @@ plainto_tsquery('avail')
) AS t1 ORDER by id DESC;

This works and return rows with tsv containing Available. But if I use avai (dropped lable) it cannot find anything.

Do all queries have to be be in dictionary? Can't we just query such letters? I have a database that contains e-mail body (content) and I would like to make it fast as its grow every second. Currently I am using

... WHERE content ~* 'letters`


texts: First Normal Form, why is it good and how does it reduce redundancy

texts:
I asked a similar question on SO and was advised to ask the type of question here. This is for a course on relational databases. A sample problem asks "how come every table in a relational database should be in First Normal Form" my first problem with this is isn't 3NF considered better so wouldn't the statement be false? My second problem with this is on SO people said that First Normal Form is good because it eliminates redundancy. I don't see how it does this because 1)all values are atomic 2)there is a primary key - neither of which eliminates redundancy.

I guess another way to ask it is why is it important for values to be atomic?


texts: how to add key and UNIQUE KEY to existing table without check to speed up performance?

texts: i have very big InnoDB tables in MYSQL 5.6 version , i want to add KEY an UNIQUE KEY as fast as i can , the data is from restored database so all keys are checked , i am looking something like SET FOREIGN_KEY_CHECKS=0; in FOREIGN KEY . is there something like this for keys ?


texts: How does one determine the number of FACT tables that are required in a data-warehouse?

texts:
When analysing data sources that need to be included in the data-warehouse (Kimball), how does a developer determine whether a schema should be a star type or constellation type?

Business processes which can be reflected at one of 3 levels (transaction, periodic snapshot or accumulating snapshot). However, there can exist business processes where the captured data is effectively identical, at any of these levels for any number of these processes.

What are the remaining considerations? (Performance..)


texts: Problem with dropping materialized views and mv logs taking a long time (or never) to complete

texts:
Apologies if it's a simple question, DBA is not my main area of expertise!

I have a materialized view that's fast refreshable, joining two tables.

When I try to drop either the MV itself or logs associated with constituent tables, it starts the process but never comes back.

I have tried truncating the log tables and MV table, and then dropping the MV, but it's the same.

There's an entry in dba_summaries for the MV.

Any clues as what is stopping Oracle from dropping it?

EDIT even this doesn't execute / return :

ALTER MATERIALIZED VIEW  STAGING_OWNER.CONTRACT_SUMMARY_MV_FR  REFRESH COMPLETE;

Version information:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0  Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production


texts: created_at vs meaningful db column name , or both?

texts:
I need often to store the datetime of an action.
For example, for a customer order, "processed_at" would be a right name.

However I need all my tables to have a created_at/updated_at fields for several reasons, among other data export (some ORM generate even the created_at/updated_at by default).

If I use created_at as standard, I wouldn't need the processed_at field as they will be the same, but in such a way I'll lose the "meaning" of the field.

But if use processed_at I'll have to customise all data export based one the relevant "_at" field.

I could create both...storage is not so expensive after all.

Just curious, what have you seen around?


texts: Issues after schemas migration from mysql 4 to mysql 5.6

texts:
Recently I've migrated some schemas from mysql 4 instance into an instance of mysql 5.6 by mysqldump and import operations. After some syntax adjustments, I could import the dump file successfully.

After the import, I went over the log file and i found some issues. Does anyone know what is that and how to fix it?

InnoDB: Error in pars0opt.cc: table cars/cars_ftr_a has prefix_len != 0
2017-08-16 17:14:15 7fd3081c4700  InnoDB: Warning: table 'cars/FTS_00000000000002cb_BEING_DELETED'
InnoDB: in InnoDB data dictionary has unknown flags 50.
2017-08-16 17:14:15 7fd3081c4700  InnoDB: Warning: table 'cars/FTS_00000000000002cb_BEING_DELETED_CACHE'
InnoDB: in InnoDB data dictionary has unknown flags 50.
2017-08-16 17:14:15 7fd3081c4700  InnoDB: Warning: table 'cars/FTS_00000000000002cb_CONFIG'
InnoDB: in InnoDB data dictionary has unknown flags 50.
2017-08-16 17:14:15 7fd3081c4700  InnoDB: Warning: table 'cars/FTS_00000000000002cb_DELETED'
InnoDB: in InnoDB data dictionary has unknown flags 50.
2017-08-16 17:14:15 7fd3081c4700  InnoDB: Warning: table 'cars/FTS_00000000000002cb_DELETED_CACHE'
InnoDB: in InnoDB data dictionary has unknown flags 50.
2017-08-16 17:14:15 7fd3081c4700  InnoDB: Warning: table 'cars/FTS_00000000000002cb_000000000000040c_INDEX_1'
InnoDB: in InnoDB data dictionary has unknown flags 40.
2017-08-16 17:14:15 7fd3081c4700  InnoDB: Warning: table 'cars/FTS_00000000000002cb_000000000000040c_INDEX_2'
InnoDB: in InnoDB data dictionary has unknown flags 40.
2017-08-16 17:14:15 7fd3081c4700  InnoDB: Warning: table 'cars/FTS_00000000000002cb_000000000000040c_INDEX_3'
InnoDB: in InnoDB data dictionary has unknown flags 40.
2017-08-16 17:14:15 7fd3081c4700  InnoDB: Warning: table 'cars/FTS_00000000000002cb_000000000000040c_INDEX_4'
InnoDB: in InnoDB data dictionary has unknown flags 40.
2017-08-16 17:14:15 7fd3081c4700  InnoDB: Warning: table 'cars/FTS_00000000000002cb_000000000000040c_INDEX_5'
InnoDB: in InnoDB data dictionary has unknown flags 40.
2017-08-16 17:14:15 7fd3081c4700  InnoDB: Warning: table 'cars/FTS_00000000000002cb_000000000000040c_INDEX_6'
InnoDB: in InnoDB data dictionary has unknown flags 40.

Edit (adding create table statement)

Create Table: CREATE TABLE `cars_ftr_a` (
  `id` int(10) unsigned NOT NULL DEFAULT '0',
  `manufacturer` varchar(100) NOT NULL DEFAULT '',
  `model` varchar(255) NOT NULL DEFAULT '',
  `object_type` int(10) unsigned NOT NULL DEFAULT '0',
  `fts_string` text NOT NULL,
  `manufactor_id` int(10) unsigned NOT NULL DEFAULT '0',
  `model_id` int(10) unsigned NOT NULL DEFAULT '0',
  `version_id` int(10) unsigned NOT NULL DEFAULT '0',
  `version` varchar(100) NOT NULL DEFAULT '',
  `group_id` int(10) unsigned NOT NULL DEFAULT '0',
  `group_name` varchar(100) DEFAULT NULL,
  `a` int(10) unsigned DEFAULT NULL,
  `b` varchar(100) NOT NULL DEFAULT '',
  `price` int(10) unsigned NOT NULL DEFAULT '0',
  `c` int(10) unsigned NOT NULL DEFAULT '0',
  `d` varchar(100) NOT NULL DEFAULT '',
  `e` float NOT NULL DEFAULT '0',
  `f` int(10) unsigned NOT NULL DEFAULT '0',
  `g` int(10) unsigned NOT NULL DEFAULT '0',
  `h` int(10) unsigned NOT NULL DEFAULT '0',
  `i` smallint(6) unsigned NOT NULL DEFAULT '0',
  `j` varchar(100) NOT NULL DEFAULT '',
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `l` int(10) unsigned NOT NULL DEFAULT '0',
  `m` int(10) unsigned NOT NULL DEFAULT '0',
  `n` varchar(100) NOT NULL DEFAULT '',
  `o` int(10) unsigned NOT NULL DEFAULT '0',
  `p` text NOT NULL,
  `q` varchar(100) NOT NULL DEFAULT '',
  `r` int(10) unsigned NOT NULL DEFAULT '0',
  `s` varchar(100) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`,`fts_string`(10)),
  FULLTEXT KEY `fts_string` (`fts_string`)
) ENGINE=InnoDB DEFAULT CHARSET=hebrew


texts: To give a user all privileges except the 'Create user' privilege

texts:
My task is simple i want to create a user in MySQL server that has all the privileges but excluding the ability to create other users.

I tried to create a user through the root account. then i gave him all the privileges, then i revoked the 'create user' privilege from this new user, then i 'flushed privileges'. i thought this is would be enough but when i logged in with the new user, i was able to create another user.

these are the codes that i used in my root account to create a new user and give him privileges except for the 'create user' privilege:

     create user 'user91'@'localhost' identified by 'google';
     grant all privileges on *.* to 'user91'@'localhost';
     revoke create user on *.* from 'user91'@'localhost';
     flush privileges;

After this i logged out of the root account and signed in with the 'user91' account and tried to create another user with this code:

     create user 'slim2000'@'localhost' identified by 'stealth';

and this code worked, what should i add in the first block of code in order to prevent 'user91' from creating other users.


texts: Allow user to do anything within his own schema but not create or drop the schema itself

texts:
I have created a schema in SQL Azure and granted the following permissions to a database role:

CREATE ROLE myrole AUTHORIZATION dbo;
EXEC sp_addrolemember 'myrole', 'myuser';

CREATE SCHEMA myschema AUTHORIZATION dbo;

GRANT ALTER, CONTROL, DELETE, EXECUTE, INSERT, REFERENCES, SELECT, UPDATE, VIEW
DEFINITION ON SCHEMA::myschema TO myrole;

GRANT CREATE TABLE, CREATE PROCEDURE, CREATE FUNCTION, CREATE VIEW TO myrole;

Through the above defined permissions myuser can create/drop his own schema, so to overcome the problem I tried the ALTER ANY SCHEMA permission. But this permission also denies the user to create/drop tables.

What permissions are required in order to allow the user to do anything within his own schema but not be able to create or drop the schema itself?


texts: Unexplained sort operation in execution plan

texts:
I am running the following query on a database with 7.5 million records:

UPDATE TestTable
SET TestCol='1234', TestCol2='1234', TestCol3='1234', Anonymised=1
WHERE [Date] >= '25 June 2016'
AND [Date] <= '25 August 2016'

For some reason the execution plan includes a sort operation on the primary key.

I wouldn't expect it to have to sort the primary key given that there is already a clustered index on that column.

Can anyone shed some light on why this is happening and what I might do to avoid the sort operation?

Edit:

Table creation script

CREATE TABLE [dbo].[TestTable](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [TestCol] [nvarchar](50) NOT NULL,
    [TestCol2] [nvarchar](50) NOT NULL,
    [TestCol3] [nvarchar](50) NOT NULL,
    [Anonymised] [tinyint] NOT NULL,
    [Date] [datetime] NOT NULL,
 CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


texts: Stored Procedure stuck at waitfor delay '00:00:02'

texts:
I have a stored procedure that's supposed to run every night.  The procedure deletes data in batches of 10k rows, and between each batch issues a WAITFOR DELAY '00:00:02' with the intention of letting other processes do things in between batches (I didn't write this, this is the intention I was given).

The job ran and has been running for a while now.  I ran sp_WhoIsActive and it shows this sp is suspened with the waitfor statement the sql text.  Why would sql server keep this from processing further?

Additional information:

It's been running for over a day.  It should have deleted 400k records, and has deleted 10k of them.

sp_whoIsActive output:

  dd hh:mm:ss.mss 01 06:32:28.126

  session_id 58

  sql_text

  login_name [redacted]

  wait_info (131ms)WAITFOR

  CPU 42,655,358

  tempdb_allocations 7,981,282

  tempdb_current 130

  blocking_session_id NULL

  reads 1,862,017,930

  writes 26,792,391

  physical_reads 273,169

  locks

  used_memory 4

  status suspended

  open_tran_count 0

  percent_complete NULL

  database_name [redacted]

  program_name SQLAgent - TSQL JobStep (Job
  0xA887960BA5034D4F89EEBDA2CB934921 : Step 2)


texts:
I'm installing Oracle 9.2.4.0 (I know, I know...) on Red Hat Enterprise Linux AS release 4 (Nahant Update 8) 64 bits using an Oracle 32 bit installer.

It appears to be stalled or hanged at 17% while "Installing Required Support Files 32 bit" indicating "linking pending".

This is what the log says as of now:

[oracle9@xxxx]$ tail -f installActions2007-09-24_07-34-38PM.log
    copyAsText = null
    JarLoc = /tmp/OraInstall2007-09-24_07-34-38PM/temp36
    gpEntries = [[libvsn_ee9.a ->%ORACLE_HOME%/lib32/libvsn9.a 2214 plats=2=>[46] langs=2=>[en,fr,ar,bn,pt_BR,bg,fr_CA,ca,hr,cs,da,nl,ar_EG,en_GB,et,fi,de,el,iw,hu,is,in,it,ja,ko,es,lv,lt,ms,es_MX,no,pl,pt,ro,ru,zh_CN,sk,sl,es_ES,sv,th,zh_TW,tr,uk,vi]]
, [libntcps9.a ->%ORACLE_HOME%/lib32/libntcps9.a 52696 plats=2=>[46] langs=2=>[en,fr,ar,bn,pt_BR,bg,fr_CA,ca,hr,cs,da,nl,ar_EG,en_GB,et,fi,de,el,iw,hu,is,in,it,ja,ko,es,lv,lt,ms,es_MX,no,pl,pt,ro,ru,zh_CN,sk,sl,es_ES,sv,th,zh_TW,tr,uk,vi]]
, [naeet.o ->%ORACLE_HOME%/lib32/naeet.o 6029 plats=2=>[46] langs=2=>[en,fr,ar,bn,pt_BR,bg,fr_CA,ca,hr,cs,da,nl,ar_EG,en_GB,et,fi,de,el,iw,hu,is,in,it,ja,ko,es,lv,lt,ms,es_MX,no,pl,pt,ro,ru,zh_CN,sk,sl,es_ES,sv,th,zh_TW,tr,uk,vi]]
]
    DllGroup = false

Llamando acción unixActions2.2.0.18.0  touchFile
    source = /folderA/oracle/folderB/OraHome1/lib/ldflags

We've successfully installed Ora9i 32 bits in a 64 bit servers before.

What do you think is the problem here?


texts: Handling exceptions with INSERT-EXEC with linked server stored proc and table results

texts:
I am trying to capture failures for a remote (via linked server) stored procedure called within a local stored procedure. If the remote procedure fails (error connecting to server, etc) I want the return value or an indicator for this so I can capture this and log it. If it doesn't fail, I want the results which would be used within the local procedure and processed as a temp table.

DECLARE @myTable TABLE(id INT,
        TransDAte date,
        Description nvarchar(25))

INSERT INTO @myTable

select * from openquery(MyLinkedServer, 'EXEC   [MyDatabase].[dbo].[RemoteStoredProc]')

The below link is on the right track but I have two differences. One, my table is multiple fields that I need to return (In the example, @RC is a list of INTs) and I am just selecting the data to use in the local stored procedure, I do not need to commit or rollback transactions.

Handling exceptions in stored procedures called using insert-exec blocks

I have tried adding catch/try statements to both the local and remote procedures but they don't capture the failures correctly.

This doesn't seem to work:

Create PROCEDURE thisTEST
AS

DECLARE @myTable TABLE(tableid INT,
        TransDAte date,
        Description nvarchar(25))

BEGIN TRY
    INSERT INTO @myTable
     select * from openquery(MyLinkedServer, 'EXEC  [MyDatabase].[dbo].[RemoteStoredProc]')
END TRY

BEGIN CATCH
    print 'Create entry in error log table'
END CATCH

-- This doesn't work because the remote procedure failure is not captured. When the procedure fails, the sproc attempts to insert the error message into the tableid field)

Create procedure RemoteStoredProc
AS

BEGIN TRY

Select transactionid, transactiondate, myDescription from SourceTable

END TRY

BEGIN CATCH
print 'Error with getting data'

END CATCH

I tried adding an output parameter but I don't know how to execute an Insert-Exec procedure with output and/or results

Create procedure RemoteStoredProcWParam(@sprocFailed INT OUT)

AS

BEGIN TRY

Select transactionid, transactiondate, myDescription from SourceTable

SET @sprocFailed = 0

END TRY

BEGIN CATCH
Set @sprocFailed = 1
print 'Error with getting data'

END CATCH

It looks like the best option is an output parameter with a cursor to store table results but I don't think that will work either.  I am used to using SSIS but don't want to have to rewrite two entire (complicated) stored procedures. Any help would be appreciated. Thanks in advance


texts: PostgreSQL: Delete Multiple Rows with multiple Conditions from the same table

texts:
I have a dilemma that makes for sort of curious experiment to be conducted but I was wondering if it already has been done.
I have a table:
create table test(action_date date, item_num int, <various irrelevant columns>,
                  primary key(action_date, item_num));

I have data that is coming in that contains the action_date, item_num and the rest of information for the <various irrelevant columns> I need to update data in the table quickly.
The fastest way I found was to:

delete the data first
insert the new data

The issue I am having is how to delete data in the fastest way possible:
The new data contains 1000s of items and 100s of dates, so I can do one of 2 things:

I can do a single query: delete from test where (item_num = item_num1 and action_date >= action_date1) or (item_num = item_num2 and action_date >= action_date2) or ...

Execute as a single transaction a set of queries of the delete from test where item_num = item_num1 and action_date >= action_date1; delete from test where item_num=item_num2 and action_date >= action_date2; ....

The question is which way would be faster?
P.S.  I could conduct experiments and see which way would be faster but I was wondering if this was already done somewhere.  My own searches yielded nothing.


texts:
I am on Oracle 12.1.0.2.0
I have a query that I wrote utilizing a CASE statement. I have 2 "WHEN" statements and one "ELSE" statement. The query is working as expected except for the "ELSE" statement. I get no syntax errors it just not hitting the "ELSE" case when it should, and instead returns nothing.

SELECT
CASE
WHEN COUNT(COLUMN_A) = 1
    THEN 'PASS'
WHEN COUNT(COLUMN_A) > 1
    THEN 'FAIL'
ELSE 'DOES NOT EXIST'
END AS TEST,
SCH.COLUMN_A
FROM SCHEMA.TABLE_A SCH
WHERE SCH.COLUMN_A = 'NA'
GROUP BY COLUMN_A

When I run this query (which in my understanding is the else case) then it returns 0 as it should:

SELECT COUNT(COLUMN_A) < 1
FROM SCHEMA.TABLE_A SCH
WHERE SCH.COLUMN_A = 'NA'

If you need to provide table structure, let me know but it is fairly straightforward.

What am I doing wrong? Why, when the query returns 0, is the else statement not returning "DOES NOT EXIST"? I have a feeling it is something simple but I am stuck and could use some help. I appreciate it.


texts: Oracle SQL Summing Values From Two Different Tables

texts:
Hey all so I am trying to add to (or subtract from) a column of different values based on a separate column in another table. Here is a example of what I am tying to do:

Table 1: (Current Inventory)

ItemName  |  Quantity
----------|----------
Baseball  |         5
Football  |        10
Jersey    |         3

Table 2: (New Shipment)

ItemName  |  Quantity
----------|----------
Baseball  |        70
Football  |       100
Jersey    |        50

How can I update my all items in Table 1 (current inventory) by adding Table 2's quantity to them?


texts: mysqldump causing database to get not saved records

texts:
I wish there was a safe way to backup mysql database. The database itself is getting not saved records if the records was about to be saved duding the dump. It is happening occasionally, maybe when the record is saved at the same time when the according DB table is being scanned for dumping.
Because of it, I'm afraid of making backup in a single transaction, I guess it will increase the chances of having lost records.
I use AWS RDS mySQL 5.6.44 and MySql-Workbench to make a dumb from production database, when the PHP scripts are busy making records. (InnoDB)
Is it expected?


texts:
I want to compare 2 dates and use CASE in WHERE to implement this logic:
if the end date is larger than 2016-06-30, the end date should be 6/30/2016.

However, I'm encountering an error. The error message is

  Missing keyword

My code is below:

    select start_date, end_date, amount from info
    where
       case end_date
       when to_char(end_date, 'yyyy-mm-dd') > '2016-06-30' then to_date(to_char('2016-06-30'), 'M/D/YYYY') as end_date
    end
    order by end_date asc

Can you help me?


texts:
I have merge replication (SQL server 2014) and a table that have 3 triggers that monitor delete, insert and update.
All of the triggers are after trigger not instead of.

I'm updating the value on one side and it doesn't update on the second server and when I'm disabling the triggers it's all working.
insert and delete doesn't work as well.
Any thoughts ?


texts:
When running Ola's full SQL Server Maintenance Solution or the individual databasebackup script I get the following error:

  MSG 207, Level 16, State 1, Procedure DatabaseBackup, Line 1545
  Invalid column name 'schemaName'.

This is part of the @databasesInParallel section which it appears to be trying to run even though this variable is set to 'N' at the beginning of the script. I haven't altered anything and this script works perfectly on other servers.


texts:
I have three variables $color, $size and $category. Each oh them can be a specific value or may refer to all entries.I want to fetch all products that are $color = "red", $size = "Medium" and any category OR any color, any size and $category = "Tshirts". I can do this using if/elseif or switch and treat all cases but this means 12 cases + the one with any color, any size and any category, so 13 total cases.

Is this even possible in mySql ??

Sample query

SELECT
*
WHERE color = $color AND size = $size AND category = $category


texts: How to add PK on every id column in Postgres

texts:
I have an issue. I have database backup for existing DB let's call it dev_db. I recovered it but under a different name like master_db and backup how I understood has only tables, columns types and etc but it hasn't system information and tables like pg_constrains table and etc that's why I lost all my PK.

So, my first question can I update all tables in my database with PK for "id" column, is it enough? (if yes how to perform that)
And my second question is how can I create a full backup of the necessary database with all system information?


texts: What is correct process to physically move an availability replica to another location

texts: Current situation is we have 3 SQL Servers with one availablity group and we plan on moving one of the secondary replicas out of HQ to another location.  There will be the span of a few hours between the transport of the server to the new location.  Would it be best to suspend the databass on the replica and then resume once its up again?


texts: MariaDB 10.5 How to Prevent High Memory Usage on Windows

texts:
I am running MariaDB 10.5 on I am having trouble with MariaDB using too much memory on my Windows 10 machine. I have 16GB of RAM, and MariaDB ends up using around 8GB.
SHOW VARIABLES; https://pastebin.com/zCuQw2Ds
I have attempted to change innodb_buffer_pool_size to small or large values. The memory usage is the same.
The program using the DB saves around 2k - 5k records into one table once per second (1 query)
around every 5 seconds a transaction runs which deletes 10k different records from the same table and inserts 10 records into another table.
All of these queries run in under 1 second.
Is the data being inserted also being cached somehow? If so, how do I limit that behavior?
Also, here is SHOW GLOBAL STATUS output: https://pastebin.com/ubCMY6Me
Any help is appreciated, thanks!


texts: how to professionally normalize my database - screenshots included

texts:
Hello database administrators.

I got some excel spreadsheet with data of "my customers" and I'd like to create professionally designed database out of it.
Can anyone tell me how this data should be normalized, what tables should be created and how should they be related with each other, so that my database would meet corporate industry standards?

I know I'm just a beginner in database creation but I've already learned about concept of database normalization, I'm eager to learn and I'll be grateful for any help and directions.

Here is the first chunk of my data imported to access table. As you can see I've already changed the job's names into numbers and I have separate table where these numbers are assigned into the jobs names.
Here it is in jobs table.

Jobs table with jobs names.

Here is the rest of the data - please help me organize it.

How about this *BESTMovie columns - do they need normalization? Some movies repeat 10 times and others don't repeat at all.

And one more question to this "*Profession" and "*BESTMovie" columns.

If I had some person, lets say clint eastwood or robert de niro, that I'd like to assign not only 3 but 5 movies, how to do this without adding another "*BESTMovie" column, where most of the fields would be blank for some other people who didn't have more than 3 movies?

I've heard about some rank column or rank table but I have no idea how should it look like.

The rest of my data looks like this:

Here is an "EyesColor" column and "Adjective" I use to describe a person. Can these columns be normalized and should the be normalized? The eyes color repeats many times of course, and adjectives repeat less frequently but they do repeat.

I hope the question is well put and I'm looking forward for answers from you guys.


texts: Connecting to Oracle SQL Developer locally on Mac?

texts:
I'm trying to connect to Oracle SQL Developer locally on my Mac. How exactly is this done? I've looked all over the network, and can't find anything helpful.

The only thing I did is installing the latest version of Oracle SQL Developer.

I have an assignment due tomorrow and cannot work from labs today.


texts:
I want to come up with some best practice for maintaining our MySQL database, versions 5.5/6 and using InnoDB.

I came across this article which is basically saying that Optimize table:

not going to show much improvement in case your queries don't use the PK.
other indexes on the table are built in a pseudo-random order and most likely would not benefit from the Optimize table.
could actually make Updates slower due to the fact that now each change have higher probability of resulting in a page split.

My questions are:

Are the 3 points above always true? Partially? Not at all?
When is it a good shot to try and Optimize a table?
In which cases Optimizing a table would not benefit or even make some uses of the table worse?
Is there a way to Optimize the indexes of a table other than its PK?


texts: Combine two event tables for multiple timelines into a single result set

texts:
This question is an extension to a question I've previously asked that was overly simplified. The more accurate example is demonstrated in this SQLFiddle, where I demonstrate a working (but slow) solution, followed by my attempt to adapt the previous answer to the actual problem.

The actual problem comes because the two tables contain events for multiple timelines.

CREATE TABLE foo (ts int, id text, foo text);
INSERT INTO foo (ts, id, foo)
VALUES
    (1, 'A', 'Lorem'),
    (1, 'B', 'ipsum'),
    (4, 'B', 'dolor'),
    (5, 'A', 'sit'),
    (8, 'A', 'amet'),
    (8, 'B', 'consectetur');

CREATE TABLE bar (ts int, id text, bar text);
INSERT INTO bar (ts, id, bar)
VALUES
    (1, 'A', 'adipiscing'),
    (5, 'B', 'elit'),
    (6, 'A', 'sed'),
    (9, 'B', 'do ');

Each table has events for timelines 'A' and 'B'. The goal is to combined the results in to a single result set showing the "state" of each timeline. The two timelines are orthogonal.

ts  id  foo         bar
1   A   Lorem       adipiscing
5   A   sit         adipiscing
6   A   sit         sed
8   A   amet        sed
1   B   ipsum       (null)
4   B   dolor       (null)
5   B   dolor       elit
8   B   consectetur elit
9   B   consectetur do


texts: Distribute n questions to m students with all students having the same questions but in different orders

texts:
I am building an online exam platform with PHP and MySQL. I have the following requirements for the database:

The exam questions are of MCQ type and there are 20 questions.
There are 30 students taking the test.

Is there a way I can have these 30 students receive the 20 questions but in different orders? E.g. that question 1 for Student A would be the question 16 for Student B?


texts:
App team had suggested to reinstall SQL native client for SQL server 2012.
My question here is can I download the SQL server native client installation file and just install over the existing native client?
Or do I need to remove the existing native client and install again?


texts:
I have large MySQL InnoDB table on my localhost. In this table primary key is id, unique index is set on fields a, b, c.

When I run query:

SELECT * FROM mytable WHERE a = 1 AND b = 2 AND c = 3;

the result is empty set, but such row certainly exists in the table.
When I add " AND id = 1234" at the end (where 1234 is id of expected result):

SELECT * FROM mytable WHERE a = 1 AND b = 2 AND c = 3 AND id = 1234;

it returns 1 row, as expected.

The only explanation is that unique index is corrupted.

But when I tried to check table with:

mysqlcheck -c mydb mytable

or with:

ANALYZE table mydb.mytable;

both checks returned OK with no errors.

So the question is:

How to identify this error in index? Is there a way to find this error with some query?

MySQL version is 5.7.23, in error.log no errrors.

Edit:

It turned out, that after creating empty table mytable1 with same structure and filling it with data by query:

INSERT INTO mytable1 (SELECT * FROM mytable);

this error is present in mytable1, which is impossible in case of index curruption.

After some research I have found, that the trouble is in field c, which has timestamp type.

Actual table structure is:

CREATE TABLE `mytable` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `a` int(10) unsigned NOT NULL,
  `b` int(10) unsigned NOT NULL,
  `c` timestamp NOT NULL DEFAULT '1999-01-01 00:00:00',
  `d` tinyint(1) NOT NULL DEFAULT '0',
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index2` (`a`,`b`,`c`,`d`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

There are screenshots of mysql queries:

Third query differs from first by 'LIKE' instead of '='

I don't know the reason why first query returned empty set, it looks like some bug in MySQL related with timestamp field.

I run query, which fixed this error:

UPDATE mytable SET c = FROM_UNIXTIME(UNIX_TIMESTAMP(c));

After this update the problem query began work fine. But actually, this update must do nothing.

Edit 2

I have found, that the cause of this problem is in clock change in Russia on October 26, 2014.

Because my local setting were:

mysql> select @@global.time_zone, @@session.time_zone;
SYSTEM | SYSTEM

so on my localhost datetime '2014-10-26 01:00:00' had two timestamps:

mysql> select from_unixtime(1414270800);
2014-10-26 01:00:00

mysql> select from_unixtime(1414274400);
2014-10-26 01:00:00

So select by value returned empty set:

mysql> select * from test_db.mytable where c = '2014-10-26 01:00:00';
Empty set

but select by id returned correct data:

mysql> select c from test_db.mytable where id = 316572297;
2014-10-26 01:00:00

I have changed default-time-zone in my.cnf to '+03:00', and everything began work fine.

Note: to reproduce error you must also have index on field c. Without it select by value c = ... works fine.


texts: How do I get Azure SQL query history/statistics/performance?

texts:
I'm migrating away from Azure SQL and my new cloud hosting service wants stats like "estimated inserts per hour on average", traffic/bandwidth usage per month, or "average read/writes on table x per day".

How can I get statistics on prior DB usage per table or database with Azure?


texts: Will Postgres perform the computation of calculated columns that aren't selected in a view?

texts:
I'm trying to understand the performance impact of selecting data from a view, where one of the columns in a view is function of other data in the original table.

Does the computation get performed irrespective of whether or not the computed column is in the list of selected columns?

If I had a table and the view declared like so

CREATE TABLE price_data (
    ticker     text,          -- Ticker of the stock
    ddate      date,          -- Date for this price
    price      float8,        -- Closing price on this date
    factor     float8         -- Factor to convert this price to USD
);

CREATE VIEW prices AS
    SELECT ticker,
           ddate,
           price,
           factor,
           price * factor as price_usd
    FROM price_data

Would that multiplication be performed in a query like the one below?

select ticker, ddate, price, factor from prices

Is there a reference that guarantees this one way or the other? I was reading the documentation on the rule system in Postgres, but I think the answer really lies with the optimiser, since nothing in the rule system documentation indicated that it wouldn't be selected.

I suspect in the above case the computation isn't performed. I changed the view to use division instead of multiplication, and inserted a 0 for factor into price_data. The query above didn't fail, but if the query was modified to select the computed column the modified query failed.

Is there any way to understand what computations are being done when a select is carried out? I guess I'm looking for something like EXPLAIN but which also tells me about the computation(s) that are being performed.


texts:
I set up a playground MySQL database for users and I'd like to prevent multiple row (large) inserts. That is, if there will be over N rows after INSERT operation, then raise an error and do not perform statement. Since INSERT may be "heavy" itself, I want to kill that query before it gets executed.

I hadn't known MySQL supports FOR EACH ROW only, but I thought about BEFORE INSERT FOR EACH STATEMENT trigger that can count rows to be inserted, sum that with row count of the table and then SET SIGNAL if sum exceedes allowed number. The problem is, I don't know how to get number of rows to be inserted, especialy when user starts INSERT INTO ... SELECT ... statement.

What other approach could I choose?


texts: How should deletions be handled in the database?

texts: I'd like to implement an "undelete" feature in a web application such that a user can change her mind and recover a deleted record. Thoughts on how to implement this? Some options I've considered are actually deleting the record in question and storing the changes in a separate audit table, or not deleting the record and using a boolean "deleted" column to mark it as deleted. The latter solution would require additional application logic to ignore the "deleted" records under normal circumstances, but would make it much easier to implement recovering the records on the application side.


texts:
I'm trying to figure out how i can get the number of commands over the last day for a sql server replication. Transactional / Snapshot / Merge.

I really want to be able to calculate the average number of new sql commands per min and latency per min but i should be able to calculate that if i can understand how to identify new commands for a replication from its history.

i have  query that i have shown below but i'm not sure if i'm on the right path.

select
his.delivered_commands as [NewCommands],
his.time as [time],
his.comments,
HIS.*
from distribution..MSdistribution_history his
inner join distribution.dbo.MSdistribution_agents mda on his.agent_id = mda.id
where mda.subscriber_db<>'virtual' and mda.publication = 'SomeReplication'
order by his.time desc

Can anyone please help on this one perhaps as i'm completely stumped.

I am using SQL Server 2008 R2.

Iffy.


texts:
I need help with this query. I have records in customer table

 Main table - M

+---------+---------+----------+
| company | Account |  Active  |
+---------+---------+----------+
| A       | A123    | Active   |
| A       | A123    | InActive |
| B       | B123    | Inactive |
+---------+---------+----------+

Reference table - R

+---------+---------+
| company | Account |
+---------+---------+
| A       | A123    |
| B       | B123    |
| C       | C123    |
+---------+---------+

Pull all records from M which exists in R but are inactive. The problem here is a company may have both an inactive and an active account in M, I want to pull only those that only have inactive account and no active account. So from the two table, I should get

+---------+---------+----------+
| company | Account |  Active  |
+---------+---------+----------+
| B       | B123    | Inactive |
+---------+---------+----------+

Because A while it does exist in R, it also has an Active Account status so I must exclude it.


texts: Granting a user account permission to create databases in PostgreSQL

texts: How do I give a user account in PostgreSQL the ability to create and drop databases?  Is there a way to do this with GRANT?


texts: Is it possible to index or cache non-immutable function (based on date)

texts:
I have a function in my Postgres database where the calculation is based on how old a record is - so given the same parameters, it always outputs a different value.

However, I'd like to index or cache the results for about an hour or two, because accuracy is much less important than read speed and load efficiency. Is this possible to do in Postgres, or do I need to use an external system to help? For example using cron to calculate a temporary table on a time interval which I access rather than using the function directly in queries. This is Postgres 11.

The basic algorithm is a measure of hotness, which is essentially a measure of popularity (measured by votes), but decaying over time (like Reddit posts).

The function code is below. It is a bit verbose; for the purpose of the question it probably could just be votes - ageInDays.

-- reddit's hotness algorithm, allegedly
-- https://medium.com/hacking-and-gonzo/how-reddit-ranking-algorithms-work-ef111e33d0d9
-- def hot(ups, downs, date):
--     s = score(ups, downs) // This is ups - downs for reddit, but we don't have downvotes
--     order = log(max(abs(s), 1), 10)
--     sign = 1 if s > 0 else -1 if s < 0 else 0
--     seconds = epoch_seconds(date) - 1134028003
--     return round(sign * order + seconds / 45000, 7)
create function epochSeconds(timestamp) returns int as $$
  select extract(epoch from $1)::int;
$$ language sql stable;

-- we do log2(n+1) instead of log10(n) since we get fewer votes than reddit
create function hotnessOrder(votes integer) returns numeric as $$
  select log(2, greatest(votes + 1, 1));
$$ language sql stable;

create function hotnessSign(votes integer) returns integer as $$
  select(case when votes > 0 then 1 else 0 end);
$$ language sql stable;

create function hotnessSeconds(creation timestamp) returns integer as $$
  select(epochSeconds(creation) - 1134028003);
$$ language sql stable;

-- We inflate hotnessSeconds divisor by a lot. Reddit's is 45000. We want good entities
-- to stay on the front page for several days
create function hotness(votes integer, creation timestamp) returns double precision as $$
  select round(
    ((hotnessSign(votes) * hotnessOrder(votes))
      + (hotnessSeconds(creation) / 5 / 45000))::numeric
  , 7)::double precision;

$$ language sql stable;

create function entity_hotness(entityId integer) returns double precision as $$

  select hotness(entity_votes(entityId), created)
  from entity
  where entity.id = entityId;

$$ language sql stable;

I'd much rather have a simple solution in Postgres that does not involve external dependencies as this project is intended to be as simple as possible. But this question is just to ask if it's possible to do without external tooling.


texts: Working on a blog creation website; should I be creating a new table for each blog, or keep everything on the same table?

texts:
So I'm working on this blog creation website as a side project. I don't actually expect it to become anything, but I want to make it as efficient as possible, even if just for practice.

The idea is that people can make accounts and then make as many blogs as they want, and post whatever they want on those blogs. My question is that should all of those blog posts be stored on the same table, or should I be creating separate tables for each new blog created? I don't know if there are limits or downsides to having hundreds or thousands of different tables, but I know that having everything on one table will slow down the selecting of posts for one specific blog, for example. This issue also applies to comments; are all comments on the site stored in one place, with an ID of what post they're about, or are they stored on separate tables depending on what blog they're on or something else. Or is there some other, better way that I should be using(without having to program a new database structure from scratch)?

I'm using PHP/MySQL for the backend. Any ideas would be much appreciated!


texts: Oracle NVARCHAR2(4000) with AL32UTF8 possible?

texts: I thought, through reading items here and elsewhere, that if my Oracle database NLS_CHARACTERSET is set to AL32UTF8, I can have a column of type NVARCHAR2(4000), but when I try to create it, I get SQL Error: ORA-00910: specified length too long for its datatype.  What is wrong with what I am trying?  Are NVARCHAR2 columns always UTF16, unlike what I have read?


texts: Getting timeouts and backup errors after mirroring and log shipping is setup

texts:
OS is Windows Server 2003 32 bit. SQL Server is SQL Server 2008 SP3 32 bit

Today I finished configuring database mirroring and log shipping for 3 databases on this SQL instance and started receiving timeouts (connection and query timeouts from our applications) and log backup errors (log backup was performed by log shipping executable). Never had this kind of issues before when database mirroring was not active and log backups were performed via our custom SSIS package instead of using log shipping (also previously log backup was setup only for 1 database every 15 minutes and now it is for 3 databases every 5 minutes).

More details:

1) Connection timeout from one of our applications:

Event Text:   System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
   at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.SqlClient.SqlConnection.Open()
   at App.Component1.EventBroadcaster.ConnectToSql(SqlConnection connect, Boolean bulk)
   at App.Component1.Broadcaster.ReBroadCastLocate(Int32 LocateId, BroadCastType bcType_)

2) Query timeout from another of our applications:

Event Text:   System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
   at App1.RTFS.DBProcess.exeNonQuery(SqlCommand cmd_)
   at App1.RTFS.RTDB.submitOrder(NewOrderParams& params_)
   at App1.RTFS.NewOrderMessageProcess.processSingle()
   at App1.RTFS.NewOrderMessageProcess.processSubmit()
   at App1.RTFS.NewOrderMessageProcess.process()
   at App1.RTFS.Application.processMessageRequest(IMessageProcess msgProcess, SessionID sessionID)

3) Log backup failure (all these are created at the same time in Event log)

  Event 3041
  BACKUP failed to complete the command BACKUP LOG DB0. Check the backup application log for detailed messages.

  Event 701
  There is insufficient system memory in resource pool 'default' to run this query.

  Event 701
  There is insufficient system memory in resource pool 'internal' to run this query.

These errors are not that frequent 5-6 times a day

I think error messages related to failed log backup ("There is insufficient system memory in resource pool") indicate possible bottleneck. From what I found possible solution is to allocate more memory for SQL Server by using AWE. However, this server only has 4GB of physical RAM and SQL Server is already using 1.8GB (as shown by task manager).

How can I better troubleshoot these issues? Is there a way to see detailed memory situation for SQL Server and trace exactly what queries/connections are timing out?


texts: Error when creating view, but everything after SELECT works

texts:
If I just run the SELECT statement all the way to the end, it selects what I want.
But if I try to make a view it says
CREATE OR REPLACE VIEW not_entered_purified AS SELECT *
FROM insert_2_purified_enz one LEFT OUTER JOIN raw_insert_2_purified_enz two
ON one.CONSTRUCT_ID = two.CONSTRUCT_ID
AND one.fk_expr_system = two.fk_expr_system
AND one.g_batch = two.g_batch
AND one.p_batch = two.p_batch
WHERE one.pk_produced_id is null;

error:
ORA-00957: duplicate column name
00957. 00000 -  "duplicate column name"
*Cause:
*Action:

Trying to alias one column didn't help.
CREATE OR REPLACE VIEW not_entered_purified AS SELECT *
FROM insert_2_purified_enz one  LEFT OUTER JOIN raw_insert_2_purified_enz two
ON one.CONSTRUCT_ID as "1" = two.CONSTRUCT_ID;

The DDL for creating the two views is this:
View one:
CREATE OR REPLACE VIEW insert_2_purified_enz AS SELECT
      ext.construct_id,
      n_term,
      enz_name,
      c_term,
      cpp,
      mutations,
      ext.g_batch,
      ext.fk_expr_system

FROM EXTERNAL ((
      construct_id NUMBER(10),
      n_term VARCHAR2 (50),
      enz_name VARCHAR2 (50),
      c_term VARCHAR2 (50),
      cpp VARCHAR2 (50),
      mutations VARCHAR2 (50),
      g_batch VARCHAR2 (50),
      fk_expr_system VARCHAR2 (50))

    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY data_to_input
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        SKIP 1
        BADFILE bad_files:'badflie_view_before_insert_purified_enz.bad'
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
        MISSING FIELD VALUES ARE NULL
        )
    LOCATION ('PURIFIED_ENZ.CSV')
    REJECT LIMIT UNLIMITED) ext

INNER JOIN produced pr
ON pr.fk_construct_id = ext.construct_id
AND pr.g_batch = ext.g_batch
AND LOWER(pr.fk_expr_system) = LOWER(ext.fk_expr_system);

View two:
CREATE OR REPLACE VIEW raw_insert_2_purified_enz AS SELECT
      ext.construct_id,
      n_term,
      enz_name,
      c_term,
      cpp,
      mutations,
      ext.g_batch,
      ext.fk_expr_system

FROM EXTERNAL ((
      construct_id NUMBER(10),
      n_term VARCHAR2 (50),
      enz_name VARCHAR2 (50),
      c_term VARCHAR2 (50),
      cpp VARCHAR2 (50),
      mutations VARCHAR2 (50),
      g_batch VARCHAR2 (50),
      fk_expr_system VARCHAR2 (50))

    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY data_to_input
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        SKIP 1
        BADFILE bad_files:'badflie_view_before_insert_purified_enz.bad'
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
        MISSING FIELD VALUES ARE NULL
        )
    LOCATION ('PURIFIED_ENZ.CSV')
    REJECT LIMIT UNLIMITED) ext;

I've also tried this, but this doesn't work either.
CREATE OR REPLACE VIEW not_entered_purified AS SELECT
      two.construct_id,
      two.n_term,
      two.enz_name,
      two.c_term,
      two.cpp,
      two.mutations,
      two.g_batch,
      two.fk_expr_system
FROM insert_2_purified_enz one  LEFT OUTER JOIN raw_insert_2_purified_enz two
ON one.CONSTRUCT_ID = two.CONSTRUCT_ID
AND one.fk_expr_system two = two.fk_expr_system two
AND one.g_batch = two.g_batch
AND one.p_batch = two.p_batch
WHERE one.pk_produced_id is null;

Error:
ORA-00920: invalid relational operator
00920. 00000 -  "invalid relational operator"
*Cause:
*Action:

What worked:
CREATE OR REPLACE VIEW not_entered_purified AS SELECT
      one.pk_produced_id,
      two.construct_id,
      two.n_term,
      two.enz_name,
      two.c_term,
      two.cpp,
      two.mutations,
      two.g_batch,
      two.fk_expr_system

FROM insert_2_purified_enz one  FULL OUTER JOIN raw_insert_2_purified_enz two
ON one.CONSTRUCT_ID = two.CONSTRUCT_ID
AND one.fk_expr_system = two.fk_expr_system
AND one.g_batch = two.g_batch
AND one.p_batch = two.p_batch
WHERE one.pk_produced_id is null;


texts: Why the concept of missing index groups is provided in SQL Server?

texts:
I know about missing indexes, and that each index belongs to a missing index group which has a unique handle identifier. I want to know why groups are useful when we can just deal with individual missing indexes and what category or attribute of an index puts multiple missing indexes in the same group(cause there is usually one index in a group). Why it is comfortable to keep several missing indexes in the same group?

Thanks for your time!


texts: Calling/Scheduling multiple SSIS jobs for ETL process - what is one fails?

texts:
So I've got a procedure:

ALTER PROCEDURE [dbo].[use___MasterLoadAll]
AS
--Must run in order
EXEC [dbo].[use__MasterLoad.Staging];
EXEC [dbo].[use__MasterApplyBRs];
EXEC [dbo].[use__MasterLoad.pdw.*];
EXEC [dbo].[use__MasterLoad.FPDW_Cube];

My issue is if the Business Rules fail on the controller I'd like to stop the procedure - Here is what's in the EXEC [dbo].[use__MasterApplyBRs]

ALTER PROCEDURE [dbo].[use__MasterApplyBRs]
AS
EXEC [dbo].[usp_ApplyDMBusinessRules];
EXEC [dbo].[usp_ApplyIndirectBusinessRules];

Then EXEC [dbo].[usp_ApplyDMBusinessRules]

ALTER PROCEDURE [dbo].[usp_ApplyDMBusinessRules]
AS
-- Set up the exection
Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'DMController.dtsx', @execution_id=@execution_id OUTPUT,
@folder_name=N'StagingImport', @project_name=N'BusRulesDirect', @use32bitruntime=False, @reference_id=Null
DECLARE @DWConnStr sql_variant = N'Data Source=DWSERVER;Initial Catalog=DataWarehouse;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;'
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=20, @parameter_name=N'ppDWConnStr', @parameter_value=@DWConnStr

-- System params
DECLARE @LOGGING_LEVEL smallint = 3
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@LOGGING_LEVEL
DECLARE @Sync smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'SYNCHRONIZED', @parameter_value=@Sync

-- Execute package
EXEC [SSISDB].[catalog].[start_execution] @execution_id

IF EXISTS (select 1 FROM ssisdb.catalog.executions where execution_id = @execution_id AND status = 4)
RAISERROR('Start with Google; End with http://dba.stackexchange.com/questions/ask', 16, 1)

Then EXEC [dbo].[usp_ApplyIndirectBusinessRules]

ALTER PROCEDURE [dbo].[usp_ApplyIndirectBusinessRules]
AS

-- Set up the exection
Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'InDirectController.dtsx', @execution_id=@execution_id OUTPUT,
@folder_name=N'StagingImport', @project_name=N'BusRulesIndirect', @use32bitruntime=False, @reference_id=Null
DECLARE @DWConnStr sql_variant = N'Data Source=DWSERVER;Initial Catalog=DataWarehouse;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;'
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=20, @parameter_name=N'ppDWConnStr', @parameter_value=@DWConnStr

-- System params
DECLARE @LOGGING_LEVEL smallint = 3
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@LOGGING_LEVEL
DECLARE @Sync smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'SYNCHRONIZED', @parameter_value=@Sync

-- Execute package
EXEC [SSISDB].[catalog].[start_execution] @execution_id

IF EXISTS (select 1 FROM ssisdb.catalog.executions where execution_id = @execution_id AND status = 4)
RAISERROR('Start with Google; End with http://dba.stackexchange.com/questions/ask', 16, 1)

So finally each of those controllers have similar TSQL that calls a bunch of Business Rules Packages - but if anyone of those packages fails the Controller will continue and end reporting failure -

Therefore if a Controller reports failure within the Business Rules I need MasterLoadAll to stop - How?


texts: If a person's name is Null then how would it break the database?

texts:
I was reading this article on BBC. It tells a story of a person named Jenifer Null and how she faces day to day problems while using online databases like booking plane tickets, net banking etc.

I am not well versed in databases and I do not use it very often. When I made a website for learning, the server side form validation used regular expressions. From what I remember it would happily accept the name "Null". I have not tried it though.

Could someone explain the technicalities when this situation would occur? Is the form validation just doing a string == NULL or something? Even so I do not think NULL is same as "NULL".


texts: MySQL: Why am I getting a syntax error when using the FIELDS ESCAPED BY clause on a select statement

texts:
I am attempting to generate a csv file including headers from a table in my MySQL 8.0 database.  After a lot of searching I found the following suggest syntax:
    SELECT "ansId","assmt","ques","answerCode","details"
    FROM ayanswer
    UNION
    SELECT ansId,assmt,ques,answerCode,details
    FROM ayanswer
    INTO OUTFILE 'c:/data/test1.csv'
    FIELDS TERMINATED by ','
    OPTIONALLY ENCLOSED BY '"'
    FIELDS ESCAPED BY '\\';

However when I run this statement in mysql I get the following error:
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FIELDS ESCAPED BY '\'' at line 9
According to the MySQL Server 8.0 reference manual this syntax should be correct.  So what am I doing wrong?


texts: Automatic Failover in Availability Group

texts:
I have 2 nodes in my SQL server fail over cluster. I use SQL Server 2012 Availability Groups for HA purpose.

The problem is that when one my server goes down the automatic fail over does not happen!

How can I enable automatic fail over?


texts: Combine date and time into one new column

texts:
I have two columns named date and time respectively. Both of them have datatype nvarchar as they were imported from ms excel. I want to combine the column into a new column named datetime. I managed to get the result as I wanted by using:

SELECT date + ' ' + time as datetime
FROM column1

But I can't insert them into the new column.


texts: How oracle syncs data across nodes in a cluster

texts:
If I have a oracle db server clustered across 2 nodes, it means both nodes have a copy of the whole data, the data is not lying half and half on each node.

Now when I make an update using my application connected to the cluster, (application deployed on WAS), i make an update on one of the nodes and then Oracle will sync it on the other node. My question is, will this sync happen in real time? Is it something configurable? It seems if it is in real time, then the performance will be affected?

What is the standard solution for that?


texts: SSIS - executing package from SQL Server by stored procedure

texts: How to execute SSIS package from stored procedure? At this moment I am able to do this by using Windows authentication. However, I must do it using SQL Server authentication, but SQL Server requires Windows authenticated user.


texts: doing a insert while selecting an specific user

texts:
I'm importing and legacy database to a new database, and changing data format. In this legacy database, I have data as following:

Interaction
id: 5458d717cd07870859000004
user_id: 5458d715cd07870859000001
interaction_type: 'like'

this is a import of a MongoDB collection to a postgres table.

In the new database, I have the table interaction as following

Interaction
id: 1
user_id: 2
interacation_type: 'like'

I connected the legacy db to the new db with postgres_fdw and I'm importing now, but how can I relate the interaction to the new table user based on old user id? I'm doing insert into interactions values(x,x,x,x) select (x,x,x,x) from interactions_legacy but I don't know how to relate the old user from interaction (5458d717cd07870859000004) to the new user (2). I was thinking in something like

insert into interactions values (user_id, other_column) select found_new_user(user_id), other_column from legacy_intearctions. Is it possible?


texts: Distributing and managing Postgres SCHEMAs with an EXTENSION?

texts:
Postgres permits distribution and management with CREATE EXTENSION. Is it more proper to write internal EXTENSIONS to manage third-party provided data, or to use scripts that DROP and CREATE SCHEMA? What are the pro's and con's to both?

Right now I tend to write,

./mySchema.psql
./mySchema/01_schema.sql
./mySchema/02_datatypes.sql
./mySchema/10_tables.sql

Etc.

And, I install all of that into SCHEMA mySchema. I'm wondering if I should change my flow and upgrade to use CREATE EXTENSION.


texts: Performance difference between on-prem SQL Server and SQL Server running on Azure(IaaS)

texts:
We are comparing the performance of an on-prem SQL Server and SQL Server running on Azure VM. The Azure VM is created by Azure Site Recovery(ASR). For the testing purpose, we have executed following TSQL on both servers. On-prem server, it executes under ~9 minutes whereas, on Azure VM, it takes ~26 minutes to complete.

declare @id as int;
declare @amount as money, @perc as real, @discount as money;
set @id = (select min(id) from tblCommissions)
while @id is not null
begin
 select @amount = sum(dollar),  @perc= sum(perc)
 from tblCommissions NOLOCK where id = @id
    update tblSales
        set Commission = Quantity * @dollar
    where id = @id ;
 set @id = (select min(id) from tblCommissions NOLOCK where id > @id)
end

Instead of while loop, I tried to rewrite it using set operation which executes in second.

UPDATE ts
SET ts.Commission = tc.Quantity * tc.dollar
FROM tblSales AS ts
INNER JOIN
(
         SELECT id, sum(dollar) dollar, sum(perc) perc
         FROM tblCommissions NOLOCK
         GROUP BY id
) tc
ON ts.id = tc.id

The dominating wait event for WHILE LOOP version is WRITELOG, however, if I use explicit commit the wait event disappears and becomes CPU bound.

As the VM on Azure was created using ASR, everything-CPU/RAM/Database Settings/SQL Server version/Patch Level should be similar. The performance remains the same no matter what kind of disks-SSD/HDD I use. I have tried to use the smallest VM size to giant 32 core VM but it makes no difference in execution time.

My question is even though the while loop is not scalable the execution time for the same set of rows should be the same for on-prem and Azure VM right? What could be wrong or where should focus from here?


texts:
I am trying to grant permissions to one of my Postgres users.
I am running CentOS with PostgreSQL, Ruby On Rails 3, ruby 1.9.3

postgres=# GRANT ALL PRIVILEGES ON loadmax TO loadmax;
ERROR:  relation "loadmax" does not exist

 usename  | usesysid | usecreatedb | usesuper | usecatupd | userepl |  passwd  |
 valuntil | useconfig
----------+----------+-------------+----------+-----------+---------+----------+
----------+-----------
 postgres |       10 | t           | t        | t         | t       | ******** |
          |
 loadmax  |    16385 | t           | t        | t         | f       | ******** |

postgres=# CREATE DATABASE loadmax;
ERROR:  database "loadmax" already exists

I am trying to migrate my database. Apparently, my user doesn't have rights to create tables.

$ cap deploy:migrate
  * ←[32m2014-03-28 13:48:39 executing `deploy:migrate'←[0m
  * ←[33mexecuting "ls -x /home/deployer/loadmax/releases"←[0m
    servers: ["108.235.52.160"]
Password:
    [108.235.52.160] executing command
    ←[2;37mcommand finished in 625ms←[0m
  * ←[33mexecuting "cd /home/deployer/loadmax/releases/20140328183628 && bundle
exec rake RAILS_ENV=production  db:migrate"←[0m
    servers: ["108.235.52.160"]
    [108.235.52.160] executing command
 ** [out :: 108.235.52.160] rake aborted!
 ** [out :: 108.235.52.160] PG::Error: ERROR:  relation "geometry_columns" does
not exist
 ** [out :: 108.235.52.160] LINE 1: SELECT * FROM geometry_columns WHERE f_table
_name='schema_mi...
 ** [out :: 108.235.52.160] ^
 ** [out :: 108.235.52.160] : SELECT * FROM geometry_columns WHERE f_table_name=
'schema_migrations'
 ** [out :: 108.235.52.160]
 ** [out :: 108.235.52.160] Tasks: TOP => db:migrate
 ** [out :: 108.235.52.160] (See full trace by running task with --trace)
    ←[2;37mcommand finished in 19619ms←[0m
failed: "sh -c 'cd /home/deployer/loadmax/releases/20140328183628 && bundle exec
 rake RAILS_ENV=production  db:migrate'" on 108.235.52.160

i have set the permissions as craig ringer suggested... but still i have the same issue and can not rake the database

[root@digihaul3-pc current]# rake db:migrate
rake aborted!
PG::Error: ERROR:  relation "geometry_columns" does not exist
LINE 1: SELECT * FROM geometry_columns WHERE f_table_name='schema_mi...
                      ^
: SELECT * FROM geometry_columns WHERE f_table_name='schema_migrations'

Tasks: TOP => db:migrate
(See full trace by running task with --trace)


texts: Identify When Dates Returned Are 1st, 2nd Or 3rd Date in Table

texts:
For the 1st 3 orders, a credit is given, so I am needing a way to flag the 1st 3 dates for a customer.  My issue that I am having with my query is it flags the first 3 dates returned regardless of if they were the first 3 dates historically.  In my sample data below, lets take custnum 123, the first 3 order dates are

01/04/2013
01/08/2013
01/22/2013

So each of those should be identified as 1st order, 2nd order, 3rd order.  No other date for custnum 123 should be flagged as historically the first three orders are the orders above.  Below is my sample data, and the query I have (which is inaccurate) that flags the first three dates the query returns NOT the first three dates historically!  What should I change in order to flag the first three dates historically?

CREATE TABLE [Calendar] ( [CalendarDate] DATETIME )

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '01/01/2013'
SET @EndDate = DATEADD(d, 1095, @StartDate)

WHILE @StartDate <= @EndDate
BEGIN
   INSERT INTO [Calendar] ( CalendarDate )
   SELECT @StartDate

   SET @StartDate = DATEADD(dd, 1, @StartDate)
END
Create Table #silly
(
  ID int IDENTITY(1,1) PRIMARY KEY,
  custnum int not null,
  cheese varchar(100),
  orderdate date
)

Insert Into #silly Values
('123', 'swiss', '01/04/2013'),
('123', 'yum', '01/08/2013'),
('123', 'aged', '01/22/2013'),
('123','typenah', '02/01/2013'),
('123', 'becher', '02/14/2014'),
('11', 'becher', '02/14/2014'),
('11', 'yum', '01/08/2013'),
('23','typenah', '02/01/2013'),
('23', 'swiss', '01/04/2013')

Declare @date1 date, @date2 date
Set @date1 = '01/31/2013'
Set @date2 = '01/15/2015'

;with datestest as (
Select
[b].[id]
,[b].[custnum]
,[b].[cheese]
,CAST([cal].[CalendarDate] As Date) As [Date],
b.orderdate
,Row_number() Over(Order by b.orderdate) rn
from [Calendar] cal
RIGHT join #silly b
ON CAST(cal.[CalendarDate] As DATE) = CAST(b.orderdate As Date)
where CAST(cal.[CalendarDate] As DATE) >= @date1
and  CAST(cal.[CalendarDate] As DATE)  <= @date2
and b.custnum = '123'
)
Select  [id]
,[custnum]
,[cheese]
,[orderdate]
,case when rn=1 then '1st Order'
when rn=2 then '2nd Order'
when rn=3 then '3rd Order'
else '' end As [Date]
from datestest


texts: Reduce query time for higher offset in sql server

texts:
Currently, I have table base_voter with data around 100M of dummy data. I have stored procedure as follows:

CREATE Procedure [dbo].[spTestingBaseVoter]
   @SortColumn    NVARCHAR(128) = N'name_voter',
   @SortDirection VARCHAR(4)    = 'asc',
   @offset INT,
   @limit INT
As
Begin
  SET NOCOUNT ON;

  -- reject any invalid sort directions:
  IF LOWER(@SortDirection) NOT IN ('asc','desc')
  BEGIN
    RAISERROR('Invalid parameter for @SortDirection: %s', 11, 1, @SortDirection);
    RETURN -1;
  END

  -- reject any unexpected column names:
  IF LOWER(@SortColumn) NOT IN (N'name_voter', N'home_street_address_1', N'home_address_city')
  BEGIN
    RAISERROR('Invalid parameter for @SortColumn: %s', 11, 1, @SortColumn);
    RETURN -1;
  END

  --SET @SortColumn = QUOTENAME(@SortColumn);

  DECLARE @sql NVARCHAR(MAX);

  SET @sql = N'SELECT id, name_voter, home_street_address_1, home_address_city
             FROM dbo.base_voter
    WITH(NOLOCK)
    WHERE deleted_at IS NULL'

  SET @sql = @sql + N' ORDER BY ' + @SortColumn + ' ' + @SortDirection +
    ' OFFSET @OF ROWS
    FETCH NEXT @LIM ROWS ONLY ';

  EXEC sp_executesql @sql,
     N'@OF int,@LIM int',
     @OF=@offset, @LIM=@limit
End

To make query faster, I have also created index as :

CREATE NONCLUSTERED INDEX FIBaseVoterWithDeletedAt
  ON dbo.base_voter (name_voter asc,home_street_address_1, home_address_city)
  WHERE deleted_at IS NULL ;

By creating this non-clustered index I have reduced the query time drastically. However, it does not applied same for the query with higher offset.

For example with:

Execute spTestingBaseVoter name_voter,asc,9999950,50

Is there something I am doing wrong, which is causing this performance issue? Or, it is more compulsory to create another index in descending order.

Let, me know if there is any better way to tackle this situation, which might decrease query time drastically.

Update:

Estimated Execution Plan


texts: Am I compromising effiency by using CTE instead of indexed tables?

texts:
Consider scenario where I am creating multiple intermediate tables in CTEs before joining the last intermediate result to some other table. The intermediate results can use joines as well.

The alternative is that I rewrite the code so that each intermediate CTE gets its own table with clustered index on columns required for efficient subsequent joins. I would expect queries on tables with indexes to perform faster.

I understand the intermediate CTE results are written to disk anyway, so I/O constraints should not be considered here, right?

Let’s assume we are considering joining tables with 50m-500m of rows in SQL Server.


texts: pt-online-schema-change deleted our data

texts:
We used pt-online-schema-change tool on number of our tables. We ran up into a problem of data inconsistency. We lost some of our data. The way we implemented was on slave and replication was also stopped while the operation was going on.

Is there anyway this tool can loose some data too during the operation, we have fairly large tables in hundreds of million of rows.


texts: Searching from array (complex criteria) without unnesting?

texts:
I have table with arrays of dates. How to search in them?

create table if not exists somedates (
startdate date,
otherdates date[]);

insert into somedates (startdate, otherdates) values
('2000-11-14', '{2001-10-30}'),
('2001-09-26', '{2002-09-20,2004-09-21,2006-09-19,2010-09-17}'),
('2005-05-10', '{2006-08-03,2008-08-12}'),
('2004-09-29', '{2005-09-29,2006-10-02,2007-10-04,2009-09-22,2010-08-06,2011-09-01,2013-09-17}');

I would want to find all rows, where in otherdates there is "1 year" (0.6 – 1.4) AND "5 year" (3.5 – 5.5) followup-dates found.

Following doesn't work:

SELECT *
FROM somedates
WHERE any(otherdates) - startdate > 0.6*365
      and any(otherdates) - startdate < 1.4*365
      and any(otherdates) - startdate > 3.5*365
      and any(otherdates) - startdate < 5.5*365
;

Following works, but if there is (and there is in the real case) lots of dates, I should put lots more OR-cases.

select * from
    (SELECT *, generate_subscripts(otherdates, 1) as s
    FROM somedates) as foo
where (otherdates[s] - startdate) >= 0.6*365
    and (otherdates[s] - startdate) <= 1.4*365
    and (
           ((otherdates[s+1] - startdate) >= 3.5*365
           and (otherdates[s+1] - startdate) <= 5.5*365)
        or ((otherdates[s+2] - startdate) >= 3.5*365
           and (otherdates[s+2] - startdate) <= 5.5*365)
        or ((otherdates[s+3] - startdate) >= 3.5*365
           and (otherdates[s+3] - startdate) <= 5.5*365))
;

2001-09-26   {'2002-09-20','2004-09-21','2006-09-19','2010-09-17'}   1
2004-09-29   {'2005-09-29','2006-10-02','2007-10-04','2009-09-22','2010-08-06','2011-09-01','2013-09-17'}   1

Using some kind of ANY-structure somehow would make more sense than needing to enumerate all indexes.

I know by unnesting I can do the search also, but surely there should be a way to do this without unnesting?


texts: Updating an FTS indexed column returns "Invalid InnoDB FTS Doc ID"

texts:
Environment:

Ubuntu 12.04 (and 13.04)
MySQL 5.6.11

I have a table which has a full text index on it (real table has much more columns and rows):

DROP TABLE IF EXISTS articles;
CREATE TABLE articles (
  FTS_DOC_ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  id INT NOT NULL ,
  title VARCHAR(200),
  body TEXT,
  UNIQUE KEY (FTS_DOC_ID)
) ENGINE=InnoDB;
CREATE FULLTEXT INDEX idx on articles (title);

INSERT INTO articles(id,title,body)
  VALUES (9, 'MySQL Tutorial','DBMS stands for DataBase ...');

MySQL documentation suggests to create the FTS_DOC_ID (with the right syntax) to prevent a full table rebuild.

So far all is good and I can query using the MATCH...AGAINST to use the FTS index. However when I need to update an indexed column:

UPDATE articles set title = 'New MySQL Tutorial'  WHERE id=9;

I get a:

Error code 182, SQL state HY000: Invalid InnoDB FTS Doc ID

If I manually take care of this column like this:

UPDATE articles a1, (SELECT MAX(FTS_DOC_ID)+1 AS ftsid FROM articles) a2
set title = 'New MySQL Tutorial', FTS_DOC_ID=ftsid  WHERE id=9;

Then the update is done. But this is not acceptable because I have several processes in parallel that update this table (though all different rows) and the risk is to get the same ftsid in different processes.
Note that updating the body column which is not indexed by the FTS index doesn't have this behaviour. I.e.:

UPDATE articles set body = 'Info: DBMS stands for DataBase ...' WHERE id=9;

successfully update the database.

Is it the expected behaviour? Or is it a bug?

I found a bug reported in the MySQL buglist about the opposite case (cannot update a non-fts indexed column but can on an fts indexed one) but not this case.


texts: MongoDB hidden node still receiving connections

texts:
I'm not sure if this question been asked before or if the following behavior of MongoDB is normal. Searching online output no results to this scenario.

Initially, we had a 3 node deployment, 1 Primary, 1 Secondary, and 1 Arbiter.

We wanted to add a ReadOnly replica to the cluster and remove the Arbiter node as well in the process. We added the following to the new node:

priority: 0
hidden: true
votes: 1
And removed the Arbiter in the same reconfiguration process so we always have 3 voting members and it leaves us with 1 Primary and 1 Secondary and 1 ReadOnly Node.

The complete process went through smoothly, however, we still end up seeing connections to the ReadOnly replica.

But when checking via db.currentOp(), no queries show up.

Based on the documentation on MongoDB website,

  Hidden members are part of a replica set but cannot become primary and
  are invisible to client applications.

Is there a way to investigate why there are connections coming in? And if this is normal behavior?

EDIT: (for further clarification)

Assuming the following:

MongoDB A (Primary): 192.168.1.50

MongoDB B (Secondary): 192.168.1.51

MongoDB C (Hidden): 192.168.1.52

Client A: 192.168.1.60

Client B: 192.168.1.61

In the logs, we see the following:

2018-03-12T07:19:11.607+0000 I ACCESS   [conn119719] Successfully authenticated as principal SOMEUSER on SOMEDB
2018-03-12T07:19:11.607+0000 I NETWORK  [conn119719] end connection 192.168.1.60 (2 connections now open)
2018-03-12T07:19:17.087+0000 I NETWORK  [listener] connection accepted from 192.168.1.60:47806 #119720 (3 connections now open)
2018-03-12T07:19:17.371+0000 I ACCESS   [conn119720] Successfully authenticated as principal SOMEUSER on SOMEDB

So if the other MongoDB instances were connecting, that would be fine, but my question is regarding why the clients are able to connect even when the hidden option is true and if that behavior is normal.

EDIT2:

To further clarify, the connection string does not include MongoDB C.

Connection String Format:

mongodb://mongodb-a.domain:27017,mongodb-b.domain:27017/?replicaSet=MyReplica

Hence, the client does not know MongoDB C exists and by theory, it should be hidden, but in our case it's not.


texts: Is it possible to change pgAdmin SQL completion shortcut?

texts:
In pgAdmin SQL query editor, the default SQL code completion shortcut is ctrl-space; however, my native language is not English, we use ctrl-space to switch input method.

Is it possible to change this completion shortcut to something else, such as tab or alt-/?

(Although I think pgAdmin is good enough, if there's good alternative, I would also be glad to know.)


texts: is it possible to show the dump history of MySQL 5.7

texts: Now I am using Canal to sync MySQL 5.7 -> MySQL 5.7 in different datacenter. The Canal would send a dump request to MySQL pretent as a MySQL slave node, Now I am not sure the dump is triggered or not, is it possible to show the master dump histroy?


texts: Is an AWS SQL Server RDS "log capture" the same as a transaction log backup?

texts:
When an SQL Server RDS instance provided by Amazon is configured for a recovery model using Full transaction logging, will the transaction log be trimmed by the "transaction log capture" that Amazon performs every 5 minutes, or is this capture not a true transaction log backup?

Thanks


texts:
There are plenty of questions on DBA.SE regarding DBCC CHECKDB and how to resolve problems when errors are returned. My specific question is on actually getting notified that DBCC CHECKDB returned errors. Most all DBAs know that you can automate the command and should run it often.

I came across this article by Cindy Gross, which has some very good notes. In it she mentions use of SQL Server Agent that if it finds errors from the execution of the CHECKDB command it will fail that step (or job depending on configuration). She points to Paul Randal's blog post on the topic here.

Now I am curious if anyone knows that the Check Database Integrity Task in a maintenance plan would do the same thing? MSDN does not mention that it will and I have not truthfully been an environment where it has come across a corruption issue; so can't say that it does. This would be versus simply setting up a SQL Agent Job with multiple steps that runs the specific command against each database, as Cindy suggested.

Thoughts? Obviously proof is in the pudding so providing more than just a guess would be helpful...