texts: Percona MySQL 5.7 | XtraBackup 2.4 - xb_load_tablespaces() failed witherror code 11

texts:
I am attempting to upgrade MySQL from 5.6 to 5.7 and everything seems to be working, but when I try to run xtrabackup I am now getting this error:

InnoDB: Allocated tablespace ID 12 for percona/checksums, old maximum was 0
InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to the directory.
xtrabackup: error: xb_load_tablespaces() failed witherror code 11

Nothing has changed, mysql user perms, system user perms, directory perms, nor structure, but now I am seeing this error. I cannot replicate the problem when I run the command as root, so it leads me to believe it is a system permission issue.

I temporarily disabled selinux to remove it as a distractor. I also granted ALL to the databacker mysql user in case it was a new permission that I was missing.

Here is the command I use:

su -s /bin/bash databacker -c "innobackupex --user=databacker --password=p4ssw0rd --socket=/var/lib/mysql/mysql.sock --stream=tar /tmp | gzip - > /tmp/wtf.tgz"

I tested perms as the databacker user and it all seems to work as expected:

su -s /bin/bash databacker -c "cd /tmp && touch test.txt"
su -s /bin/bash databacker -c "cd /var/lib/mysql && touch test.txt"

What am I missing?


texts: Validate three digits before decimal and after decimal point six digits

texts:
I have columns called longitude and latitude with nvarchar data type. The user can enter something like (23.23424432),(432423.343), which should not be allowed.

Can anyone help me to validate this in SQL Server?

Data that can be entered by user: Ex: 0, 12, 101.0, 111.022222
Data that can’t be entered by user:  Ex: 1111, 111.2222222, 1234.1111

It should accept only this format: (232.343244)

Below code is accepting any numbers:

 + CASE WHEN (CAST(A.GPS_LATITUDE AS NVARCHAR) IS NOT NULL AND CAST(A.GPS_LATITUDE AS NVARCHAR) <>'' AND CAST(A.GPS_LATITUDE AS NVARCHAR) NOT LIKE'%*%')
AND ISNUMERIC(CAST(A.GPS_LATITUDE AS NVARCHAR)+'e0') = 0 OR (LEN(A.GPS_LATITUDE)>20)
 THEN 'Invalid Latitude. ' ELSE '' END
+ CASE WHEN (CAST(A.GPS_LONGITUDE AS NVARCHAR) IS NOT NULL AND CAST(A.GPS_LONGITUDE AS NVARCHAR) <>'' AND CAST(A.GPS_LONGITUDE AS NVARCHAR) NOT LIKE'%*%')
AND ISNUMERIC(CAST(A.GPS_LONGITUDE AS NVARCHAR)+'e0') = 0 OR (LEN(A.GPS_LONGITUDE)>20)
 THEN 'Invalid Longitude. ' ELSE '' END

In my application we had a download template button. When we press the download template button, it downloads a template with only a header - with columns like   LONGITUDE and LATITUDE. We need to enter manually like 234.234325 but user can enter 2342.234242 or 4353453.123213 and save the Excel file.

In the application UI, there will be an upload button. Pressing the upload button sends the data to the database and it validates in a stored procedure. In the stored procedure, the above lines of code validate the data. We need to add decimal number validation like (432.342342) for above code.


texts: How to represent aggregates in a data warehouse

texts:
I am building the dimensional model for a data warehouse (as an exercise for a mini-course I am doing) and I want to build an aggregate to speed up queries.

Basically, I want the aggregate to group by:

(PizzaType), (Size), (Month), (Year)

I would have num_pizza_types * num_sizes * num_months * num_years aggregate rows.  One row for all combinations of that.  But how does this change my dimensional model (or aggregate schema, I suppose)?  How is this represented in the dimensional model?  Do I remove all columns except month/year from my Date dimension?  Do I remove all columns except Size and PizzaType from my Pizza dimension?


texts: xtrabackup prepare - after dump or before restore?

texts:
i have doubts about xtrabackup --apply-log.
is there any difference when use it? should it be done right after dump or before restore (even few months later)? seems stupid, but maybe i missing something.

documentation says this:

  Prepare a backup in BACKUP-DIR by applying the transaction log file named xtrabackup_logfile located in the same directory. Also, create new transaction logs. The InnoDB configuration is read from the file backup-my.cnf created by innobackupex when the backup was made.

thanks for help :)


texts: Unable to install mysql 5.6.17and configure mysql on Win 7 -64 bit Os

texts:
Already seen this post but not my type of question as iam stuck in the installation itself

I am trying to install MySQL 5.6.17 on Windows 7 64-bit Operating System. Everytime I try I am stuck in this step where the installer installs the products.

Things I have tried--

Every time the installation starts it gets stuck in this step upon
which I delete the AppData from my C:/ drive and try to install it
again and this comes up again.
In the above image you can see the Connector /ODBC 5.3.2 is not
installed when I click Try again initially starts and then the same.
screen.Untill and unless I can install this driver I can not proceed
with the installation.
Tried the previous version of MySQL 5.0 but i coud not configure
properly (some port no conflict) and deleted the AppDAta and tried
again but with same issue.


texts: pgAdmin 4, no built-in SSH tunnel config?

texts:
Just tried using pgAdmin 4 for the first time.
It looks like there's no built-in SSH tunnel config for connection.
I searched the docs but there's no mention of the feature.

Is it gone for good?


texts: Change Tracking Retention from a backup?

texts:
I have a backup of a SQL Server 2008 database with change tracking enabled on it. The retention period is 2 days. AutoCleanUp is set to true.

The database backup is 5 days old and has 2 days worth of change tracking information when it was backed up 5 days ago.

If I restore the database today, would I lose all change tracking information from the database?


texts: Merge two huge tables keeping only the unique rows

texts:
I have two tables: table1 with 61 million rows and table2 with 59 millions rows. The columns in both are the same (name and type). Both are imported from backup files.

I want to merge these two tables into one table keeping only unique records.

For example, in table1 I have the following records:

NAME    CODE   ...
Name1   001    ...
Name2   002    ...
Name3   003    ...
Name4   004    ...

And in table2:

NAME    CODE   ...
Name1   001    ...
Name2   002    ...
Name5   005    ...
Name2   002    ...

The result table should be something like this:

NAME    CODE   ...
Name1   001    ...
Name2   002    ...
Name3   003    ...
Name4   004    ...
Name5   005    ...

Edit to provide more info as requested by @Erwin Brandstetter:

table1 has 15 GB. I am inserting records on table2 but when I finish it will have almost the same size.
I have more than 300 GB of free space (and an external HD if needed).
Total RAM is 8 GB.
No indexes (i can create if needed).
No concurrent access (it is accessed just by me on local machine).
I can create a new, third table if needed.
Here if the full table definition:

CREATE TABLE inss
(
   nb double precision,
   nome character varying(200),
   nasc double precision,
   cpf double precision,
   especie double precision,
   dib double precision,
   valor double precision,
   banco_pagt double precision,
   ag_banco double precision,
   orgao_pag double precision,
   aps double precision,
   meio_pagto double precision,
   banco_empr double precision,
   contrato character varying(200),
   vl_empres double precision,
   comp_ini_d double precision,
   parcelas double precision,
   vl_parcela double precision,
   tipo_empre double precision,
   endereco character varying(200),
   bairro character varying(200),
   municipio character varying(200),
   uf character varying(2),
   cep double precision,
   sit_empres double precision,
   dt_averb double precision,
   dt_exc double precision,
   id double precision
 );

The id column is filled with a row number, so it could be used to perform batch operations if the full operation becomes too slow.
All columns except the id column should be considered for the unique check.


texts:
I'm working on a school project. And I'm required to implement it using Java-EE and SQL Server. I want the design to withstand any kind of faulty user requests. My questions are how should I design the tables and the relationships such that deletions are safe.
In addition i want to know how to log these changes.


texts: Does ROLLBACK TRAN honor LOCK_TIMEOUT value?

texts:
I have a following stored procedure on my system.

BEGIN TRY
  BEGIN TRAN
  SET LOCK_TIMEOUT 10000
  UPDATE tbl
  COMMIT TRAN
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRAN
END CATCH

UPDATE tbl went into timeout and ROLLBACK TRAN also failed because of timeout.

Question is: is this possible?


texts: SSMS IntelliSense doesn't work on particular database

texts:
I have SSMS 2014 installed on my desktop, and many 2012 and 2014 databases running in our data center.

So far I found one database (database A) doesn't have IntelliSense working, for exampletyping:

SELECT [put cursor here] FROM sys.objects

and moving cursor between select and from, pressing CTRL + J, I can see popup options for other databases, just not for database A. I have checked all options and refresh local cache, all don't work,  and I am using an account which has sysadmin permission.

The compatibility level is 11.0
Version is

  Microsoft SQL Server 2012 - 11.0.5556.0 (X64) Oct 31 2014 16:50:24 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3  (Build 9600: )

I can log on to the db server which is in US and see IntelliSense working there, it just doesn't on my desktop (in Australia). Is it most likely a latency issue? is there a timeout limit or setting for this feature?


texts: What is the reason to "normalize your databases"?

texts:
In the past, I've been told (on this site) that I should normalize the values in the database - using a lookup table instead of using direct (string) keys.
I am confused why this is so good that several people recommended this. Is it just for memory consumption? But then in my case (explained below) how much is that?
Consider I have a dictionary database for a website:
CREATE TABLE dictionary
(
    id serial NOT NULL,
    key text NOT NULL,
    language text NOT NULL,
    value text,
    PRIMARY KEY (id)
)

And then insertion would happen like:
INSERT INTO public.dictionary VALUES
    ('yes_button', 'en', 'yes'), ('yes_button', 'nl', 'ja')

Or instead of 'en' I'd use 'en-us'. Now I've been told to "normalize" the database - which would mean having a lookup table that binds the string representation of a language ('en', 'nl' to a value):
CREATE TABLE languages
(
    id serial NOT NULL,
    language text
)

CREATE TABLE dictionary
(
    id serial NOT NULL,
    key text NOT NULL,
    language integer NOT NULL,
    value text,
    PRIMARY KEY (id),
    FOREIGN KEY (language)
        REFERENCES  public.languages (id)
)

However, this would increase complexity quite a bit, since insertion can no longer be simple — it needs to either check the foreign table on the backend, or use some more complex SQL. So there is a real cost to updating to this design.
What are the advantages?

Is it just the storage size? An integer foreign key reference is 4 bytes anyways, and a size 2 string is 3 bytes (while a size 5 is 6 bytes - so save 2 bytes at most).

Is it speed of database? But isn't this then a micro-optimization, which is the "root of all evil"?

Is it just to make sure that each language "exists" before insertion? There are other mechanisms, and languages should be created on the fly anyway. Existence isn't based on what is in our database, but rather an external lookup to the standards, and the language is properly added once a user provides a single translation.

Examples of the idea were mentioned in the comments on my previous question Improving database design, is this a valid case for entity attribute values?:

language should an int and refer to a seperate table(normalizing your tables) – nbk Jan 13 at 16:54

@nbk interesting -> why? The ISO country + language codes are already unique so a string can just as well be the key right? Why would I use an extra integer redirection, when the textual description is as strong a guarantee of uniqueness? – paul23 Jan 13 at 17:58

@paul23 a normqalized table like in my desription or in the answer helps to reduce size and speed of the querys lets say you have 255 language and you save an int that reduce the size massively. A normalized data design helps noto to have redundant data in your tables – nbk Jan 13 at 18:20

The dictionary database is actually separate from the data database, since it is used by several applications.
If I use string keys through a normalizing table, the 'en-us', 'nl-nl'...  might very well be perfect keys themselves. They are unique on their own, so why would I use a uuid or other randomized string? In other words, 'en-us' isn't just a string, it's specifically determined by ISO 639 standard as a locale string. Thus, the amount of strings is very much limited. It very much serves as an "identifier" like "pi" is for 3.1415...
Regarding data quality, I wouldn't have to use an integer key — I could use a string — but then the string 'en-us' is the correct string to represent the "English as spoken in US" locale.


texts:
SQL services wont start due to incorrect drive letter after windows restore

Had a server crash, was successfully able to reinstall windows and restore the server SQL and all the necessary services were already installed.

Only problem the windows drives letters are wrong. So the services are pointed to the incorrect drive letter. I cant change the services in reedit or change the drive letter in disk management. Is there a way to do a repair in SQL add remove programs to associate's the necessary services with the correct drive letter?

Here's a example of the services
Path to executable
I:\Program Files\Microsoft SQL Server\MSSQL.1\

only problem the actual drive letter is E:

Services that wont start
SQL SERVER
SQL SERVER AGENT
SQL Server Analysis
SQL Server Analysis Services
SQL Server Integration Services
SQL Server Reporting Services


texts: How do I connect to localDB from SQL Server 2014 Express

texts:
I downloaded both the LocalDB and Management Studio for MS SQL Server 2014 Express so that I can practice on my local machine.  However, when I open Management Studio and try to browse to localDB to connect to to start created databases I can't locate the localDB.

May someone please assist me with this?


texts: SQL Server 2008, Sum two columns from different tables

texts:
I'm trying to return a table with only 3 columns: CMP, CODE and Totalization.

On the first table I need to Sum all the amount data and then group by CODE.

Then I query a second table and get the code and Forecast.

Finally, I need to sum the sum_cash with Forecast and group them by code.

The snippet below works for MySQL, but in SQL server, it keeps giving me this error:

  "Column 'cash.sum_cash' is invalid in the select list because it is not contained in either an aggregate function or group by.

I'm really open to modify the code as long as I can get those 3 columns.

I'm Pretty sure the problem relies on the "Totalization" thing. But I'm no expert on SQL server, or any other SQL language, so I really need help for this one.

SELECT
    cash.CMP as 'Name',
    cash.CODE as 'Code',
    (cash.sum_cash + bal.FORECAST) as 'Totalization'
From(
        Select
            CMP,
            CODE,
            sum(CASE when BUDGET in ('4','25') then AMOUNT else AMOUNT * -1 end) sum_cash
            From TEST1
            where Nature=12
            GROUP BY CODE
        ) cash,
        (
        SELECT
            CODE,
            FORECAST
            FROM TEST2
            where BALANCE_TYPE=-2
        ) bal
GROUP BY cash.CMP, cash.CODE;


texts: Better solution for creating many-to-many table per entity

texts:
I have a table for images.

I have tables for entities such as people, cars, houses, trains and dogs to which I need to assign images.

I need to assign images to entities. The obvious solution is to create many many-many tables:

people_images (person_id, image_id, sort_position, ...)
cars_images   (car_id,    image_id, sort_position, ...)
houses_images (house_id,  image_id, sort_position, ...)

And perhaps modify the images table like so:

images (image_id, url, is_person_image, is_cars_image, is_houses_image, ...)

I am wondering if there is a better solution or common pattern for this.


texts: Java application unable to access Blob stream in Oracle after upgrade to 11g

texts:
This issue below might be an application problem, but I cannot seem to rule out a poor database setup, I was hoping to get some insights into potential database issues.

I am maintaining a legacy Java application, our hosting provider provides DBA support for our Oracle database. We were on 10g, several months ago we upgraded to 11g. I in turn upgraded the Java drivers from ojdbc5.jar to ojdbc6.jar. Everything mostly seems fine....

The application uses Hibernate ORM tool, one of the fields of this one entity happens to be a Blob type. When the application saves changes to this entity, the generated SQL is an update statement that includes this Blob column regardless if it has changed or not.

On this one operation, the user updates their annual subscription, so they do it once a year, but when it calls the update here Java throws "Unable to access blob stream!" and then claims the underlying connection is closed.  This ONLY occurs for this one operation.

If I completely shut down and restart the server, and then perform the same operation again it works perfectly fine and continues to work fine for that one record forever and ever amen.

But it seems to happen here for every single record in this one table at least once.

Needless to say this is a nightmare to debug because I cannot reproduce it on a single record, just observe it once per record on my test system, then never see it happen again, and yes the same behavior occurs on my test environment that is a mirror of my production database.

My question is if it is possible that when the Oracle 11g upgrade occurred that it corrupted the LOB space in some way to where this seems to be happening?

EDIT:

Now I can't reproduce it on any record....
its like the database felt that I was trying to debug it and temporarily reverted into a state of good behavior

Maybe the LOB space was sleeping, then woke up?

EDIT 2:

The problem happened again this morning, after I left the database to sit unused for several hours.

As requested here is the exact error:

  Unable to access blob stream!
  Cause: Closed Connection
  SQL State: 08003
  Vendor Code: 17008

Preliminary research seems to suggest that this might be an issue with the Oracle JDBC drivers?  Oracle JDBC 11.2.0.3 ojdbc6.jar


texts: Logical Backup strategy in large Oracle Databases

texts: in Oracle databases, I would like to know if it is advisable to make logical backups with exp or expdp in large databases with large volumes of data, greater than terabyte, I have seen several systems with Oracle that have large sizes and only perform physical backups With RMAN, I wonder what the strategy would be if a table was deleted for some reason ...? What do you do in such cases? What would be a save strategy in these large Oracle?


texts: reverse the characters of a string without changing the position of the numeric value in T-SQL

texts:
With T-SQL, I'm trying to find the easiest way to reverse the characters of a string without changing the position of the numeric value.

So for string:

abc223de11

have

edc223ba11


texts: Allow user to drop only tables / views created by that user in MySQL

texts: Is it possible to allow a user to only drop tables and views that he/she created in MySQL? How is this achieved?


texts: T-SQL Syntax error copying views dynamically

texts:
I'm creating a stored procedure to copy all views from one database to another using the following code.

ALTER PROCEDURE [dbo].[spCreateViews] (@SourceDB NVARCHAR(200), @DestinationDB NVARCHAR(200))
AS
BEGIN
    DECLARE @SQL2 NVARCHAR(MAX)
    SET @SourceDB = REPLACE(REPLACE(@SourceDB, '[', ''), ']','')
    SET @DestinationDB = REPLACE(REPLACE(@DestinationDB, '[', ''), ']','')

    SET @SQL2 =
        'USE [' + @SourceDB + ']
        BEGIN
        DECLARE @SQL NVARCHAR(MAX)
        DECLARE CUR_V CURSOR FOR
        SELECT sc.text
        FROM sys.views av
            JOIN sys.syscomments sc ON sc.id = av.object_id
        OPEN CUR_V
        FETCH NEXT FROM CUR_V INTO @SQL
        WHILE @@FETCH_STATUS = 0
        BEGIN
            EXEC sp_executesql N''USE [' + @DestinationDB + '] EXEC sp_executesql '' + @SQL +  ''''
            FETCH NEXT FROM CUR_V INTO @SQL
        END
        CLOSE CUR_V
        DEALLOCATE CUR_V
        END'

    EXEC sp_executesql @SQL2
END

The stored procedure is returning the error INCORRECT SYNTAX NEAR '+'

I'm receiving the error at the first + before @SQL

As far as I can tell this syntax should be correct.

Here is the output for @SQL2 to help get rid of some of the quote nesting in case that is helpful.

USE [SOURCEDBNAME]
BEGIN
    DECLARE @SQL NVARCHAR(MAX)
    DECLARE CUR_V CURSOR FOR
    SELECT sc.text
    FROM sys.views av
        JOIN sys.syscomments sc ON sc.id = av.object_id
    OPEN CUR_V
    FETCH NEXT FROM CUR_V INTO @SQL
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC sp_executesql N'USE [DESTINATIONDBNAME] EXEC sp_executesql ' + @SQL +  ''
        FETCH NEXT FROM CUR_V INTO @SQL
    END
    CLOSE CUR_V
    DEALLOCATE CUR_V
END


texts: Copy few rows from one database to another

texts:
I have two PostgreSQL databases on two different machines and the network connection is slow.
I would like to copy few selected rows from the first instance to the second. Unfortunately, the full dump of single table is too big. A CLI solution would be fine.


texts: Finding related functional dependencies from Amstrong's axioms

texts:
Let be

F = { AB → C,
       B → D,
      CD → E,
      CE → GH,
       G → A }

some functional dependencies.

Do we have, thanks to Amstrong's axioms or its corollaries AB → G?

My attempt

I don't think so because

AB → C
CE → GH

Then from the pseudo-transitivity rule:

  {X → Y, WY → Z}⊨XW → Z

Then

ABE → GH

And tht's all. We can't get rid of H.


texts:
DATALENGTH() function returns the length of the data in BYTES.

The table is like this:

create table tb3
(
id int,
id2 varchar(100),
nome varchar(200)
)

| id      | id2   | nome  |   |
|---------|-------|-------|---|
| 1       | 1     | r     |   |
| 1       | 11    | r1    |   |
| 1       | 111   | r11   |   |
| 1       | 1111  | r111  |   |
| 1       | 11111 | r1111 |   |
| 11      | 11111 | r1111 |   |
| 1111111 | 11111 | r1111 |   |

but when i run it like this:

select DATALENGTH(id), DATALENGTH(id2), DATALENGTH(nome) from tb3

| (No column name) | (No column name) | (No column name) |   |
|------------------|------------------|------------------|---|
| 4                | 1                | 1                |   |
| 4                | 2                | 2                |   |
| 4                | 3                | 3                |   |
| 4                | 4                | 4                |   |
| 4                | 5                | 5                |   |
| 4                | 5                | 5                |   |
| 4                | 5                | 5                |

Why it says each varchar character is using only 1 byte?
If i'm not mistaken, a varchar uses 2 bytes + number of characters.


texts: Reducing table size during initial load with ADR in SQL Server

texts:
When a table is created, and loaded with data from scratch based on a file, the table size changes significantly when Accelerated Database Recovery is on in SQL Server (realized based on my question here, Table size in Azure SQL Managed Instance vs On Premise SQL Server)

Is there any way (perhaps via a hint) to reduce table size in a database with ADR on (as it cannot be turned off in our case) only during an initial load? There is no possibility of conflict during this initial load, and all of the relevant data can be loaded at once.

Thanks!


texts: How to change Oracle user password without showing the actual password on screen?

texts:
Is there a way you can change Oracle user password without showing the actual password on the screen? I understand that you can execute ALTER USER user_name IDENTIFIED BY new_password; but this will show the password on screen.

My organization practices split-password for administrator password and two person would need to key in the password. How do I allow them to key in the password without any of them viewing any portion of the password?

Thank you.


texts: Issues adding Suscriptions and Viewing them

texts:
I am working on generating scripts to create a Transactional Replica. It seems to be working.
I created an item, and it gest replicated in the Rep Database. However the subscriber is not showing in the server.
Both Subscriber and publisher are in the same server. But when I expand the Local Publications folder I can see the subscriber, but when I expand the Local Subscriptions folder, it doesn't show anything.
What could I be doing wrong? This is the script I am running:
use [mydatabase]
exec sp_addsubscription @publication = N'MySubscription', @subscriber = N'Server-BD001', @destination_db = N'my_rep_db', @subscription_type = N'Push', @sync_type = N'replication support only', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'MyPublication', @subscriber = N'Server-BD001', @subscriber_db = N'my_rep_db', @job_login = N'Server\Repuser', @job_password = 'Password', @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4, @frequency_subday_interval = 5, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @dts_package_location = N'Distributor'
GO


texts: MS SQL : 'Ghost' file reference in sys.master_files

texts:
I'm not a DBA and have almost zero knowledge of databases.

One database on my SQL Server system would not start. I was getting an error like:

FCB::Open failed: Could not open file XXXXXX
for file number 2.  OS error: 5(Access is denied.)

Since this database is simply for testing, I dropped it and tried to recreate it.

The process I followed is: drop database, stop mssql service, delete any remaining data files, start service.

Worked fine for a while, but after a reboot I'm back to the same issue.

When I inspected this further, I can see that I have a 'ghost' reference to the file in sys.master_files. In other words, I drop the database and I still see a reference to this file here.

I thought to just delete the reference like this:

DELETE FROM sys.master_files where database_id = 6

But I get this:

[Code: 259, SQL State: S1000]  Ad hoc updates to system catalogs are not allowed.

I did check permissions etc, and the first error was nothing to do with that.

If I could just find a way to delete this reference I'm sure the problem would be solved.


texts: Is a manual log file grow procedure working different that autogrowth?

texts:
I wonder if the internal methods of growing a file using

ALTER DATABASE testdb
MODIFY FILE
(
        NAME = testdb_Log
      , SIZE = 40960MB  --40 GB
)

work different then letting the file autogrow to the same size over time?

Background of my question:
I face very high (>10K) VLF counts on several Log files of different live DBs with full recovery that are sized around 100 GB.

While testing strategies to get this fixed, I resized a Log file to 16 MB having 4 VLFs and let it grow to 40960 MB then with an AUTOGROW setting of 100 MB. Afterwards I had 20 VLF in the file.

My expectation was to see additional

  (40960 MB - 16 MB) / 100 MB * (8 VLF per autogrow) = 3276 VLF in the
  file.

Instead I now have only 20. Why?

Second part of my question:
So far I understood that an inappropriate AUTOGROW setting cause the high VLF counts, that is defenitely the case in our dbs where this setting has not been looked after for a long period of time. All the articles explaining how to handle this situation advice to choose a proper value for AUTOGROW. But if I am right assuming that if the manual grow process (first part of this post) does not even use the autogrow setting, and I grow the log file this way to a proper size that does not require any automatic growing, then the autogrow settings is not that important at all afterwards?

Third part of my question?
Is the VLF count of 20 on a 40GB log file now too low? Is this the reason I read we should increment the file back up again in increments? What VLF count might be a GOOD (performant) number for a 40 G or 80 G log file?


texts: Is it possible to purge the whole content of Oracle's SYS.INDEX_STATS view?

texts:
My purpose is to analyze the indexes of a schema for getting the best basic index compression to apply.

I'm going to use the command

ANALYZE INDEX OWNER.INDEX_NAME VALIDATE STRUCTURE;

That will fill up SYS.INDEX_STATS view with the info I need, but considering this view does not have the OWNER column, this is not helping me. Here's why:

In my Oracle instance I have many schemas containing the installation of the same product filled with data from different customers.

I'm filling INDEX_STATS by looping DBA_INDEXES and calling ANALYZE INDEX, so any call to ANALYZE INDEX OWNER.INDEX_NAME VALIDATE STRUCTURE will overwrite the entry from the previous call on the same index on a different schema.

I may save the content of INDEX_STATS after having analyzed each schema, but for not all the customers have the same tables installed because of modularization of the software, some entries of INDEX_STATS may refer to previous calls to a different schema.

Is there a way for purging the content of INDEX_STATS so I can have a clean situation before analyzing each schema? Or the only solution is to join INDEX_STATS with DBA_INDEXES for saving the content of INDEX_STATS to a final table containing the data from all the schemas?


texts:
I am having some issues with one of my tables in a production server.

I know there are many diffrent threads running on this table. I need to put some kind of monitoring tool to see how many locks are happening on this table, and which queries are causing this locks.

I am familiar of slow-query-log.

Are there any tools which can help me?  I have seen monyog, are there any free tools maybe?

Also is the a way to know all the deadlocks that happened overtime, like `show engine innodb status' deadlocks, but over time.


texts: Creating Multiple Tables with WITH statements

texts:
I have created Table1 and Table2 both with WITH statements. I have been trying to create Table3 with WITH statement as well that would be the UNION ALL of Table1 and Table2.

Here is my attempt:

 WITH table3 AS
 (
    SELECT c1 as c3
    FROM table1
      union all
    SELECT c2 FROM table2
 ), table2(c2, loc2) AS
    ( SELECT
        character2, location FROM lotrfirstencounters
    ), table1(c1, loc1) AS
    ( SELECT
        character1, location FROM lotrfirstencounters
    )

SELECT DISTINCT * FROM table3 ORDER BY c3;

So when I had WITH statement right before table2 and before attempting to create table3; both table1 and table2 were created as intended and I was able to call their distinct column like this:

  WITH table2(c2, loc2) AS
( SELECT
    character2, location FROM lotrfirstencounters
), table1(c1, loc1) AS
( SELECT
    character1, location FROM lotrfirstencounters
)

SELECT DISTINCT * FROM table1 ORDER BY c1;

I tried different ways to get a UNION ALL between them to get distinct columns for a new table but nothing I've tried seems to work.


texts: Join multiple tables without overhead on the DB Server using PDO

texts:
I have multiple tables called light_readings, temp_readings, co2_readings and humidity_readings.

The column format of the tables are as below:

light_readings:

Id, light_value, unitid, date_time

temp_readings:

Id, temp_value, unitid, date_time

co2_readings:

Id, co2_value, unitid, date_time

humidity_readings:

Id, humidity_value, unitid, date_time

I am using PDO to fetch these values to my webapp, but retrieving these using multiple SQL queries is putting too much load on my DB server. I need to get the latest readings of all the tables in just one row if possible.

I tried using UNION, but even that takes up some load on the server

(SELECT temp_value FROM temp_readings where unitid='SS0007' order by Id desc limit 1)
UNION ALL
(SELECT humid_value FROM humid_readings where unitid='SS0007' order by Id desc limit 1)
UNION ALL
(SELECT co2_value FROM co2_readings where unitid='SS0007' order by Id desc limit 1)
UNION ALL
(SELECT light_value FROM light_readings where unitid='SS0007' order by Id desc limit 1)

Could someone assist me with this?


texts: Maintaining replicated data in a staging-to-warehouse environment

texts:
I am trying to come up with a way to move metric data from the staging server to the data warehouse servers.

I have a bunch of dimension tables that gets populated during the ETL process (on the staging server), these are being shifted to all the data warehouse servers (in a scale-out environment) using transactional replication... this is working fine due to the nature of these tables (basically since they are dimension tables, they can just be copied over as-is to all servers).

Now am having issues moving the ETL'ed data in the metrics (or data) tables to the warehouse server from the staging server...

because this type of data requires data (from the staging server) to be "Merged" (as in, Update, Insert, Delete) with the existing data in the warehouse server, and once that's done, the data in the staging server that has just been processed needs to be deleted (so it staging server doesn't keep building up data and becoming huge). The data in the data-warehouse must be maintained when the data in the staging server is truncated (so if data is deleted in the staging environment, it doesn't get deleted in the warehouse).

The only way I can think of doing this is by moving the data from "staging server" to "staging table on the data-warehouse server" (through replication), then perform the merge in the data-warehouse to the proper metric data warehousing tables. In this case the main data warehousing tables aren't involved in the replication directly, and so data in those tables doesn't get deleted once I truncate the metric tables in the staging server.

It just sounds in-efficient!, isn't there a way to get replication to handle all this (maybe through using clever replication filters?)

Maybe I shouldn't use replication at all, and just do custom linked server queries that shift and truncates data?, although from experience linked server queries tend not to perform very well.

Any guidance or just general idea dumps would go a long way!

P.S. Sorry if this isn't clear, any questions you have just fire them over


texts: How to declare and use variables in Informix

texts:
How can I declare and use variables in Informix Database. I'm using version 9.40uc7 and I would like to create two date variables read values into them then compare them using an if statement.
eg.DECLARE first_date, last_date DATE;

However, I keep getting a syntax error on the declare line.


texts: Is there an option to prioritize a table while replication conflict occurs?

texts:
I have a master slave configuration for my production stage environment. Sometimes I need to write to some tables of my stage server i.e slave server. In that case conflicts occurs as content from both master and slave differs. So is there any option that for some particular tables if conflict occurs I want to give priority to the content of the tables in slave server. So that it will not be overridden by the data from master server.

Note: I am very new to database and devops. So please bear me and let me know if it is a noob question.


texts:
I'm finally taking the plunge and moving up from Junior DBA at a large organisation to Senior DBA at a smaller place running SQL Server. It's going to take some time for me to get a good picture of the businesses' requirements and things might be in a bit of a state as they've been without a DBA for 6 months or so.

Is there any advice you would give to a someone starting in this position barring the things like producing a list of SQL Server instances, finding out restore point and recovery time targets, and trying to decipher what state things were left in?

Thanks!


texts: Refactoring Series of UNIONs in single table (SQL/SYBASE)

texts:
I am trying to query a database and reformat the rows/columns that I am interested in. Each row in the current table (EQ) represents a field in a form. In reality, there are several hundred FieldNum values; each EntityID "has" every FieldNum. Each FieldNum has a corresponding MemoText value. In my example below, FieldNum 1,2,3 are related, as are 4,5,6.

EQ and the resulting table should look like:

I need to create a single row out of multiple FieldNum values, but I am unsure how to do this without using UNION ALL. It seems incredibly inefficient to use seven or eight UNION statements, considering all the data I am interested in is located in a single table. Alas, I am unsure how to refactor the code to make it more efficient. A sample of my code is below.

Select En.EntityNum , En.EntityID,
Test.Date, Test.Name1, Test.Name2

FROM En
JOIN
(SELECT EntityNum,
Max(Case WHEN FieldNum = 1 Then Memotext End) as Date,
Max(Case WHEN FieldNum = 2 Then Memotext End) as Name1,
Max(Case WHEN FieldNum = 3 Then Memotext End) as Name2
FROM EQ  WHERE Group by EntityNum
UNION ALL
(SELECT EntityNum,
Max(Case WHEN FieldNum = 4 Then Memotext End) as Date,
Max(Case WHEN FieldNum = 5 Then Memotext End) as Name1,
Max(Case WHEN FieldNum = 6 Then Memotext End) as Name2
FROM EQ  WHERE
Group by EntityNum
FROM EQ Group by EntityNum
) as Test ON test.EntityNum = En.EntityNum

I tried to generalize this question as much as possible, but if it is still too specific, a generalized answer would be completely satisfactory.


texts:
I am trying to go from the first table: test4 (pid, event, time) and count how many events transition to another (in second table eventtransitions).

For instance, in the final table, there should be a +1 count for prev = A and event = B because the first two rows has A happening before B and the pid for both are 1. The issue I am having right now is that my query only increments once, but should actually increment as many times as the transitions occur. The final table should have prev = A event = B with count = 2.

Test4 table:

 pid | event |        time
-----+-------+---------------------
   1 | A     | 2016-09-23 02:21:13
   1 | B     | 2016-09-23 03:24:13
   1 | A     | 2016-09-23 04:21:13
   1 | B     | 2016-09-23 05:21:13

Evventtransitions table:

 prev | event | count
------+-------+-------
 C    | B     |     0
 C    | A     |     0
 A    | C     |     0
 B    | C     |     0
 A    | B     |     1
 B    | A     |     1

The query I tried is this:

update eventtransitions
    set count = count + 1
from
(select rank() over (order by pid, time) as rank,
    pid, event, time
    from test4) as a
join
(select rank() over (order by pid, time) as rank,
    pid, event, time
    from test4) as b
    on a.rank = b.rank - 1
    where eventtransitions.event = b.event and prev = a.event
    and a.pid = b.pid;


texts: Customize psqlrc to be different based on host or username

texts:
Hi I would like to have my DB prompt a different color if I am logged into production or with a user with full permissions.

So I would like something like this (which doesn't work) in my psqlrc:

if [%M == PROD]; then
     \set PROMPT1 '%[%033[1;35m%]%M:PROD_IS_RED%[%033[0m%] %n@%/%R%#%x '
else
     \set PROMPT1 '%[%033[1;32m%]%M:NORMAL%[%033[0m%] %n@%/%R%#%x '
fi

Is there any way to make something like this work ? Is there some alternative can I make a script that runs psql with a different psqlrc file? like psql --psqlrc_file=psqlrc2


texts: I have a database server I am using an old dump from to restore due to the need of older records that must be brought back. I also need to sync to this database the new entries from the current database server. How would I accomplish this?


texts: ERROR! MySQL is running but PID file could not be found - MariaDB 10.1 - CentOS 7 - Fresh VM

texts:
So I'm having a little trouble with a BRAND new VM I just created today to test a Galera Cluster.

What I have done so far is...

Download and install the latest CentOS 7 in a VM.
Install open-vm-tools and configure SSH
Install MariaDB Repo and install MariaDB 10.1 via

    cat << 'EOL' >/etc/yum.repos.d/MariaDB.repo
    # MariaDB 10.1 CentOS repository list - created 2016-12-21 16:21 UTC
    # http://downloads.mariadb.org/mariadb/repositories/
    [mariadb]
    name = MariaDB
    baseurl = http://yum.mariadb.org/10.1/centos7-amd64
    gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
    gpgcheck=1
    EOL
    yum install MariaDB-server MariaDB-client

Now I get this error, but I am able to get to the MariaDB CMD Prompt which says its running, but I can't have this error on a brand new setup.

[root@localhost ~]# mysql -uroot
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.1.20-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> exit;
Bye
[root@localhost ~]# /etc/init.d/mysql status
ERROR! MySQL is running but PID file could not be found


texts:
I have a problem with an Oracle 11g instance.
Every connection that has been idle for about 20 minutes are lost.
I get the following error

ORA-03135: connection lost contact

I did the following actions to get rid of it:

Changed the value os the IDLE_TIME in DEFAULT profile to UNLIMITED
Checked the TIMEOUT parametes in sqlnet.ora. There are no one setted
Checked the TIMEOUT parametes in listener.ora. There are no one setted

This problem only happens remotely.

What more I can do?

Thanks.


texts: Always On AG is Down when service is stopped

texts:
I have SQL Server 2014 running on Windows Server 2012 R2.
There are three nodes, one is remote DataCenter. So two nodes have voting.
Failover Cluster is using file share witness on remote server.
If primary server is down, AG automatic Failover works but when SQL Server is down AG automatic failover does not work. AG waits on Resolving state.
Also:

I stopped service from configuration manager, AG Failover is success.
Stopped service from services.msc, AG Failover is failed.
Killed service from Proccess List, AG Failover is failed.

Voting;

Server         Assigned Vote       Current Vote
Node 1              1                   1
Node 2              1                   1
Node 3              0                   0

I disabled firewall for all network, nothing changed, problem continuous.
Can someone help me to solve problem?

Critical Error
The role of this availability replica is unhealthy.
The replica does not have either the primary or secondary role.

Failover is from node1 to node2. Both are set to synchronous and automatic failover. Nodes are not in a paused state. I have installed all nodes as stand alone instances.


texts: Ola Hallengren's SQL Server Maintenance Solution & CMD Syntax

texts:
I'm using the free version of SQL Server Management Studio so I have to use CMD to call the SQL scripts.  The official page gives an example of the CMD syntax used to call the "DatabaseBackup" script, as follows:

Create cmd files to execute the stored procedures; for example: sqlcmd
-E -S .\SQLEXPRESS -d master -Q "EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES', @Directory = N'C:\Backup', @BackupType = 'FULL'" -b -o C:\Log\DatabaseBackup.txt

I got that to work, but I can't get it to work for the "DatabaseIntegrityCheck" or the "IndexOptimize" scripts.
I guess I don't know the syntax.  I tried using the example code and just replacing the script name, but that didn't work.
Can anyone help me?


texts:
I tried searching for this but couldn't find an existing question about this. I'm considering implementing Brent Ozar's suggestion to backup minutely for our critical databases. I don't have a great test environment where I can try this and get useful results. I'm wondering if the log backup job runs over one minute, what happens to the next minutely backup job(s). In these situations, I'm assuming the jobs will fail but I'm worried they'll queue up and I could wind up with a vicious circle where I can never get caught up on the backups. Can anyone else advise if how SQL Server will handle this situation? Also, if the jobs fail, does it fail "elegantly" (i.e., the backup chain is not broken and I can continue to restore using the logs) or will it bugger it up and break the backup chain?

Also, if I have full and differentials scheduled throughout the day as well, and a minutely log is running will it keep the full and diffs for running?

I appreciate any help you all can provide.


texts: PostgreSQL - partial dump of large objects (pg_largeobject table)

texts:
Context : Our web-app is like a collaboration platform where you can share files, appointments, survey and so on. An instance of this web-app is divided in communities, each one is independent to each other but share the same PostgreSQL database.

Target : actually what i'm trying to do is to write a shell script in order to make regular backups of communities separately. This means that I need to select  only part of the data inside the various Postgre tables and export/dump the community data.

Obstacle :
My big problem is with the pg_largeobjects table, used to store the BLOB of files. This table use OIDs as pk (something I don't relly like by the way). The table files (containing files metadata) , has a reference to the pg_largeobject OID.

Solutions considered :

Select all the OIDs from files table WHERE community_id = something.
And afterwards, I could export the data stream from pg_largeobjects using [lo_export][1] function giving all the OIDs selected in the previous operation:

  int lo_export(PGconn *conn, Oid lobjId, const char *filename);

  The problem with this approach is when i will try to restore pg_largeobject records, I need to keep the same OIDs or the relation will be broken. I can use the function lo_import_with_oid that permit to define the desired OID, but in most cases OIDs will be already occupied from the original data, and for this reason the import will generate a new OID, that will not satisfact the relation with the rest of the database.

I still don't know how I could achieve this, but i try to explain:

during the export I could select and save all the PK of the files table record of the certain community (let's call it filesPKs).
I will then perform a [lo_export] of pg_largeobjects
and during the lo_import I could ignore the fact that new OID are being generated. But in someway i would save the list of these new OIDs inserted.
as final step I could update all the rows(using filesPKs reference) in the files table in order to make them point to the new imported pg_largeobject records.

This second procedure in my opinion is very difficult, because I still don't see how could I update files rows in order to point to the correct largeobject.

An other option, that fancifully would be the best one, would be to modify the pg_largeobject table in order to have an additional column with a (static) reference to files table. But after a test it seems to be impossible:

  testdb=# alter table pg_largeobject add column prova text;  ERROR:
  permission denied: "pg_largeobject" is a system catalog

  Questions :

Any one else faced this issue?
Am I just missing something, or taking the wrong approach?
Out there exist an other way to export/dump pg_largeobjects records, exporting all the fields, not only the data stream?


texts: How to schedule a job using windows task scheduler to export data from potgresql eriodically into CSV file.?

texts:
I am facing an issue even after following all the steps as suggested in the answer provided by Neil Mcguigan for the question asked In the above link
I was getting an error - "psql: f_sendauth no password supplied." When I tried to run a psql command from CMD in Windows server.
I have created pgpass.conf and placed in the path - *C:\users\myname\AppData\Roaming\postgresql*
I have set the environment variable  PGPASSFILE and given the above path.
The command I am using looks like this.
psql -w -c "my command to export data from a table in postgre database and save it in a CSV in shared folder" --host myhostname ----dbname mydbname --username my username

-w command in postgre should ideally stop from prompting for a password.
What am I missing..!? Please help


texts: Easy or Schedulable way to move data from Production to Test

texts:
What I am looking to do, is schedule a copy of data from my production database to my dev/test database.

The dev/test database will be newer than the production database in terms of schema, but the production database has current data. I'm in a bit of a bind as I need to test my database changes against production size data, and it would be very helpful to have current data.

I'm on SQL Server 2008 R2 Standard for both production and development environments; is there a way I can create a job on my development server to "suck" data from my production database in a read-only fashion? I'd like a script that can copy table data wholesale, ignoring missing columns and ignoring any constraints in the target table. A script that does this for one table is all I really need, I can modify it to fit my tables and duplicate it to run for all my tables. The issues I'm having is finding anything even remotely similar to this.

This is a bit of a follow-up on my last question of the same nature; I have since got my database into source control, and I'm liking it much better. The problem is that I still face a stale data issue on my development server, so I'd like to find a way to schedule updates.

I'm open to alternate ways to achieve this, but it needs to be something I can run on a schedule and something I can build with notepad and/or SSMS or other tool available with Standard Edition SQL Server.


texts: How do you Audit Login Failures? Solutions?


texts: Localization: One table or multiple tables? (MySQL)

texts:
I need to be able to store string messages in various languages. I guess I need a column for each language string.
For example my columns could be:

ID | message_EN | message_DE | message_FR

Now, I have strings in various tables in the DB. Should I use one large string table with the above structure (or similar) where most other tables will be joined (via string ID), or have multiple tables with the same structure each related to one table?
Most probably there will not be much overlap between the data of multiple tables.
Are there any performance considerations with using one large table?

Thank you.


texts: Exporting table from MySQL 5.7 to MySQL 5.6 (Error)

texts:
While running the following queries on MySQL 5.6.39,

DROP TABLE IF EXISTS `sessions`;

CREATE TABLE `sessions` (
  `id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_id` int(10) UNSIGNED DEFAULT NULL,
  `ip_address` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_agent` text COLLATE utf8mb4_unicode_ci,
  `payload` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `last_activity` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `sessions`
  ADD UNIQUE KEY `sessions_id_unique` (`id`);

I get the following error :

However, if I remove the part that says COLLATE=utf8mb4_unicode_ci, everything works fine. Why is this happening ?

The above query is an export from phpMyAdmin (MySQL version 5.7.22)


texts: Why does mysql treat "<" like it was "<="?

texts:
I've tried the following command:

SELECT * FROM `mi_schedule` WHERE `day`<=(DATE_ADD(NOW(),INTERVAL 7 DAY)) AND `day`>=NOW()

Got 8 rows. Then I changed the <= to <, and nothing changed, still 8 rows.

So I changed the interval from 7 to 6:

SELECT * FROM `mi_schedule` WHERE `day`<(DATE_ADD(NOW(),INTERVAL 6 DAY)) AND `day`>=NOW()

and I got one less row from the results (7 total), as expected.

I switched back from < to <=, and again nothing changed.

I.e. apparently the < is considered to be a <= instead.

Is that intentional? According to the docs, it shouldn't.

PS: day is a DATE type.

(using version 5.5.29, engine InnoDB)


texts: How to access to other INSERTed/UPDATEd rows from trigger in PostgreSQL?

texts:
Solution in MS SQL

MS SQL trigger functions have deleted and inserted system tables in which all rows affected by operation are stored. You can count updated rows:

set @updatedCount = (select count(*) from deleted)

or find out minimum value:

set @updatedMinimumCol1 = (select min(col1) from deleted)

Problem with PostgreSQL

For FOR EACH ROW triggers I can use OLD and NEW system records, but they store only 1 row for each call of trigger. Calls of trigger are separated, so if user updates 10 rows, trigger will be called 10 times, but each time I can know only about 1 current row, not about all 10 rows.

For FOR EACH STATEMENT I do not know any mechanism of access to updated rows at all. I use PostgreSQL v9.6, OLD TABLE and NEW TABLE were introduced in v10.

  PostgreSQL does not allow the old and new tables to be referenced in statement-level triggers, i.e., the tables that contain all the old and/or new rows, which are referred to by the OLD TABLE and NEW TABLE clauses in the SQL standard.

Try with transaction_timestamp() additional column

I can add special column with DEFAULT transaction_timestamp() to the main table and then use it to distinguish just-now-updated rows from other, but it is not a solution since multiple INSERTs/UPDATEs can be in one transaction and they will have the same timestamp of transaction. Probably I could clear this timestamp column in trigger after each statement to avoid this problem, but how to do this if such clearing will emit update trigger again - will be infinite update trigger calling.

So, this try was failed.

Bad solution in PostgreSQL

The only way I know is that:

First, use FOR EACH ROW trigger to collect current statistics (min and count) like aggregate functions. I use temp table to store it between calls (this trigger gets called 1 time for each row). But we will not know which row is last (when time will come to use this statistics).

CREATE TEMP TABLE IF NOT EXISTS _stats (
  _current_min   int,
  _current_count int
) ON COMMIT DROP;

IF EXISTS(SELECT 1 FROM _stats LIMIT 1) THEN
  --Current row is not first, there is statistics for previous rows.
  UPDATE _stats
  SET _current_min   = (CASE WHEN NEW.col1 < _current_min THEN NEW.col1
                        ELSE _current_min END)
    , _current_count = _current_count + 1;
ELSE
  --There is no stats because current row is first for this INSERT/UPDATE
  INSERT INTO _stats (_current_min, _current_count)
  VALUES (NEW.col1, 1);
END IF;

Second, use FOR EACH STATEMENT trigger to use collected statistics. Do not forget to clear temp table (if user will run multiple INSERTs/UPDATEs in one transaction, old statistics will remain in temp table and corrupt all next calculations!).

For more complex tasks we can create temp tables inserted and deleted on the same way as _stats.

The workaround

In PostgreSQL we can use RETURNING clause for INSERT/UPDATE/DELETE to obtain new values of all rows affected by operation. Then we can manipulate with them, but each function with INSERTs/UPDATEs have to implement this technology ===> 1. additional code in functions with such INSERTs/UPDATEs - duplication of the RETURNING; 2. we can forget to implement such technology to new function; 3. data will be corrupted since required manipulations will not be called automatically (like triggers will).

The question

Maybe, you know a better way to access all rows affected by INSERT/UPDATE?


texts: Should a support incident be opened for every stack dump, or just ones that cause an outage?

texts: Assuming that the stack dump in question can't be mapped to a know issue, if a stack dump doesn't seem to cause a problem and everything is working afterwards, should a support incident be opened for it?  Or should incidents be opened only when it causes an outage or loss of functionality?


texts:
I have a somewhat strange question.

Is there a way to manually force PostgreSQL into recovery mode?
I've not been able to find anything in the documentation.

I've been working with a script monitoring a PostgreSQL database and need to do some testing.


texts: Set of datetime versioned rows, how to build the most up to date row

texts:
I created this SQL fiddle with the sample data

The sample data is this:

--
-- Table structure for table `dogs`
--

CREATE TABLE IF NOT EXISTS `dogs` (
  `version_stamp` datetime NOT NULL,
  `dog_id` int(11) unsigned NOT NULL,
  `dog_name` varchar(30) DEFAULT NULL,
  `dog_color` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`version_stamp`,`dog_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `dogs`
--

INSERT INTO `dogs` (`version_stamp`, `dog_id`, `dog_name`, `dog_color`) VALUES
('2014-09-28 09:30:24', 19849, 'Ruediger', 'black'),
('2014-09-25 02:15:23', 19850, NULL, 'white'),
('2014-09-25 02:16:41', 19850, 'Bello', NULL),
('2014-09-26 02:15:23', 19850, NULL, 'brown'),
('2014-09-27 02:16:41', 19850, 'Dieter', NULL);

What I want, is the newest version of a row, e.g. dog row.

Say I enter the dog_id 19850 with date time stamp

2014-09-26 02:15:24

Then I want to get :
Bello brown

When I use a different timestamp, say:

2099-12-12 12:12:12

I want to get

Dieter, brown

Is there a way to do this in SQL?


texts: Postgres: I set log_hostname on but still client_hostname is null in pg_stat_activity

texts:
I have this problem with Postgres.

I need to get the client_hostname from pg_stat_activity. I set log_hostname=on on the server but still client_hostname is null in pg_stat_activity.

What am I missing?


texts: SQL Server 2008 Database Restore Error - Cannot open backup device 15105

texts:
I am trying to restore from a backup and getting the following error

  Msg 3201, Level 16, State 2, Line 1
  Cannot open backup device 'Z:\FULL_BACKUP.bak'. Operating system error 3(failed to retrieve text for this error. Reason: 15105).
  Msg 3013, Level 16, State 1, Line 1
  RESTORE DATABASE is terminating abnormally.

The code that I used in SQL Server is as follows (I also tried using the dialog screens and got the same result)

RESTORE DATABASE DEX
FROM DISK = 'Z:\FULL_BACKUP.bak'
WITH MOVE 'Decri' TO 'E:\Test\DEX.mdf',
MOVE 'Decri_log' TO 'E:\Test\DEX.LDF'

It may be worth noting that the "FULL_BACKUP" is created by the same SQL server, without any issues (so I assume that the server has permissions).

Drive Z is a network path, for which there is 'full access' to all domain users.

The full backup is 200Gb

Drive E (local) has 500Gb free space

Any help would be appreciated.


texts: In an Oracle 11 database I have a table that all fields are varchar2. This table is linked in an access database, and when I try to do an insert from a data aggregate query, it throws the error ‘can bind a LONG value only for insert into a LONG column’. If I try the same insert in sqlplus it works perfectly. If I try copy the row (Ctrl+C and Ctrl+V) without the field I think is incorrect, it inserts correctly, and then, if I type the same string trying to insert, don’t throw the error. Reading other posts, it seems it’s an encoding error, but I don’t know where can change it.


texts: How to prevent a login from 'listing' a table or view definition in PostgreSQL?

texts:
Is there a way to prevent a login from listing the tables and columns in a schema?

I have to give access to a remote login to query on a single view; however, I also must make sure that such login cannot list every object on that schema.

Is there something like MS SQL SERVER's DENY VIEW ANY DEFINITION TO public; on PostgreSQL?


texts:
I've been working with SQL queries for a couple of weeks and I'm trying to learn and understand from my mistakes. I have a table with two columns IsInvisible (bit) and ShortName (string) and when one is changed to update the other one also like: - if added a ~ in front of the ShortName to set IsInvisible to 1 and if ~ is removed to set it to 0 - if IsInvisible is set to 1 to add ~ in front of the ShortName and when set to 0 to remove it.

I've tried something like:

ALTER TRIGGER [dbo].[updateInvisibility]
ON [dbo].[table]
AFTER UPDATE
AS
BEGIN

UPDATE t
SET IsInvisible = (CASE WHEN i.ShortName like '~%' THEN 1 ELSE 0 END),
    ShortName = (CASE WHEN i.IsInvisible = 1 AND t.ShortName NOT LIKE '~%'
                 THEN '~' + t.ShortName
                 ELSE t.ShortName
            END)
FROM table t JOIN
     inserted i
     ON t.Id = i.Id;

end

My problem is that when I'm updating one of the columns or both, nothing happens and I get this error:

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)

What am I doing wrong?

The "Allow Triggers to Fire Others" option displays "True".


texts: Database Doesn't Contain Current Data After Restoring Last Full Backup from Production

texts: I restored my last full backup from production database to reporting database server, but last restored database on my reporting server doesn't contain current tables, procedures, functions etc. What can cause this situation? How can I solve it?


texts: Not authorized on admin to execute command mongoDB: Atlas M0 Free Tier cluster

texts:
My MongoDB shell has version v3.6.5 (the same with older versions) & MongoDB server has version: 3.6.8
I have MongoDB M0 Free Tier cluster and I want to use from shell different commands.
So, I created admin user with password:

Set up IP Whitelist:

And then connect via MongoDB shell using admin user:

Up to this point, everything is fine, but when I started to use commands I get always the issue:

I checked the answer from here and tried to create admin user directly from mongodb shell using command, but also the same error:

Why it happens? If I has already created admin user? Can someone explain me, thanks a lot in advance.


texts: Advice on Measures that have properties on their own

texts:
I'm looking for advice on how to model a database when the measures have an increasing number of properties - the values of which could be considered dimensions in their own right.

In a simplified case, let's say I have StateId and Sales:

StateId    Sales
----------------
1          20
2          25

Then later I need to store information about what type of measure 'Sales', a custom group it needs to be in, and say, an aggregation rule.

What would be a good way to model this? I could have a Measure table which links to other dimensions that contain Types and Groups.

Id  Name    TypeId  GroupId    Agg
-----------------------------------
1   Sales   1       5          Sum

But then I'd need to store what type of dimension it was, whether it relates to a real dimension or one that relates to a measure.

Curious as to how others have solved the problem of storing meta-data for a measure.


texts: Consolidate to multiple or single instance SQL Server on a VM with many third party vendor databases?

texts:
I need to consolidate several older instances to SQL Server 2016 Enterprise on a single VM. Since all of the databases are third party vendor systems, they are a sprawling mess of SQL Agent jobs, custom roles, dynamically created users, and demands for doing installs/upgrades with SA access that I have to fight frequently.

I yearn for the isolation of multiple instances, but hear that they will create resource contention and other woes. In a VM environment where resources such as I/O would be shared across all VMs anyways and being able to set max memory per instance, should I still be avoiding multiple instances?

Breaking out to separate VMs is not an option for me due to licensing costs. Also, the scale I'm looking at is about 15 different vendor platforms, some of which I know could happily sit in the default instance together without the above-mentioned issues, so I would probably have a maximum of 5 instances if I went that route.


texts: Performance of XML to Table Function in Stored Procedures

texts:
In a number of the databases I'm helping to maintain, there is a pattern where the code passes in a list of IDs as an XML string to a stored procedure. A user-defined function turns them into a table which is then used to match IDs. The function that accomplishes this looks like this:

ALTER function [dbo].[XMLIdentifiers] (@xml xml)
returns table
as
return (
    --get the ids from the xml
    select Item.value('.', 'int') as id from @xml.nodes('//id') as T(Item)
)

This works fine when we test it in a wide variety of scenarios in SSMS. But if it's called within a stored procedure, it will run extremely slowly and the execution plan will show it spending the large majority of time parsing these IDs. This is true whether we write the results to a temp table, join to them or use them in a subquery.

Example from an execution plan using the above function:

Can anyone offer insight as to why we are seeing such poor performance from these queries? Is there a better way to parse the values from XML? Most of the databases using this pattern are on SQL Server 2014 or 2016.


texts: Attach 110(2012) from SQL Server 2014 Instance to a (110) SQL Server 2012 INSTANCE

texts:
I have a SQL Server database on a 2014 instance with compatibility set to 110. When I try to attach it to a SQL Server 2012 instance it says cannot attach version 782 to 706.

I thought I could attach a compatibility of 110 from a SQL Server 2014 to a SQL Server 2012 since in the 2014 the compatibility to 110

Any help would be appreciated.


texts: How to Select the First (or last) entry for a specific day

texts:
I have a table "AttendanceRecords" with entries like this

EntryID UUID PRIMARY KEY,
StafID UUID REFERENCES staffmembers(ID) NOT NULL,
ArrivalTime TIMESTAMP NOT NULL,
DepartureTime TIMESTAMP

For one function I want to find the first and last entry for a specified staff member on a specified date.  To Clarify, First would be as per

SELECT MIN(ArrivalTime)
FROM AttendanceREcords
WHERE StaffID = 'xxxxx';

But I'm looking for how to compose a query that will do

SELECT EntryID
FROM AttendanceREcords
WHERE StaffID = 'xxxxx'
AND ArrivalTime = MIN(ArrivalTime);

On a related report I would like to find all the entries where the arrival-times are on a specified date, eg sorted by StaffID

What would the queries look like for these (Using Postgres 9.3)


texts: What information from v$ views should be included when asking questions about Oracle Problems?

texts:
I was asking more information about Operating System. Then I wondered if an generic sql can give this information? Following things come to my mind.

SELECT banner from v$version;
SELECT LOG_MODE ,PLATFORM_ID,PLATFORM_NAME FROM V$DATABASE;

My output from test database is below.

SQL> SELECT banner from V$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> SELECT LOG_MODE ,PLATFORM_ID,PLATFORM_NAME FROM V$DATABASE;

LOG_MODE     PLATFORM_ID  PLATFORM_NAME
------------ -----------  -------------------
NOARCHIVELOG          10  Linux IA (32-bit)


texts: Log Shipping - Secondary Database - Can CDC be enabled?

texts:
I was reading this article about SQL Server log shipping and the statement was made that log shipping supports limited read-only access to secondary databases (during the interval between restore jobs).

The article doesn't go on to mention any other limitations, if any, which apply to the secondary database. Specifically, I would like to know if 1) CDC could be enabled on the secondary database; 2) Could triggers be applied to some tables on the secondary database.

Essentially I'm trying to determine, via T-SQL, for any given table, what rows were inserted, updated and/or deleted during the last restore job.

SQL Server 2014 is being used. Also note that CDC is not enabled on the primary and the company is adamant that it not be enabled for the primary. However, they would have no issues enabling it for the secondary, if it's possible.


texts: How to find out who is active and locking the oracle database?

texts:
At work, we are sharing oracle credentials among many developers. Often time team runs heavy adhoc  queries on the database and it is causing lot of trouble to oracle server. We are working to fix this issue permanently by creating readonly replica instance. Mean time I wanted to find out if I can quickly identify who are all currently in session by looking at windows user id, and/or machine names etc, so that we don't need to maintain too many credentials for database.

Also is it great if I can find out which user/query is creating trouble for oracle server. For example one query caused temp space outage in server because we had not limited the resource for the account.


texts: SSIS database source imports as BLOB incorrectly

texts:
Friends,

I am a beginner user to SSIS (Microsoft SQL Server Integration Services). While I have experience coding in other languages (Python, MATLAB, R, and others), I have never experienced a language whose data types are so confusing to me.

I have uploaded data to a database that I created using a Data Flow object. The data type of the column in SSIS is a Unicode string [DT_WSTR] and the data type of the column in the database is ntext.

After uploading the data to the database, I am retrieving it from that database in a new Data Flow object. However, when it loads the data from the database into SSIS, it is now a Binary Large Object Block (BLOB). It seems near impossible for me to be able to do any sort of transformation to the column with it as a BLOB object.

Is there any way to either convert the BLOB object to DT_WSTR? Or to a useable datatype?

Any help is appreciated!


texts: SQLite - What are .acd, .ard and related file types?

texts:
I'm using a SQLite database, and the main database file is:

Config.acd

and there are also files named:

Config.acd-shm
Config.acd-wal

What is the .acd file type, what are those variants (acd-shm, acd-wal), and why is the main file not a .db file?

I also have the following files, what are these:

Raw.ard
Raw.ard-shm
Raw.ard-wal


texts:
I am trying to write a stored procedure that imports a file but what I've noticed is that COPY can be followed by string literal and no variable?

I am trying to achieve something like this

FUNCTION insert_file (IN filepath VARCHAR(255)) ...
   COPY filepath ...

is it even possible or I need to do some magic?


texts: Should we need to use Barriers on a production database (MySQL/InnoDB)?

texts:
Even though we are using a journaling filesystem (EXT3) with barrier enabled, is this still safer and recommended?

e.g

mount -o barrier=1 /dev/sda /mntpnt

Reference:

http://lwn.net/Articles/283161/
https://serverfault.com/questions/403891/journaled-filesystems-and-power-failure


texts: In SQL Server, in layman's terms, how do you lock resources well enough to perform an INSERT-IF-NOT-EXISTS transaction?

texts:
If someone asks how to perform an INSERT-IF-NOT-EXISTS operation in SQL Server, they'll typically get an answer like this back:
IF NOT EXISTS(SELECT 1 FROM [TheTable] WHERE [ColumnX] = @valX)
    INSERT [TheTable] ([ColumnX]) VALUES (@valX)

The problem I'm seeing with this is that in between the SELECT statement and the INSERT statement, the situation could change externally.  Another process could insert the ColumnX value after the SELECT statement, but before the INSERT statement, resulting in an error being raised.
I've worked in software for a while, but am not a DB specialist, and when I search for an answer to this problem in SQL Server, the results I'm seeing are either irrelevant or quite difficult to really apply (because they're either answering a different question or are written in terms tailored to DB specialists).
So in layman's terms, how do you resolve this problem?  I did get a little bit rusty with SQL in recent history, but am thinking that there really should be a pragmatic locking mechanism to use for this (whether there is or isn't).  As a fallback, maybe error handling can specifically determine whether an error raised matches this exact issue, ignoring it in that specific case.
Preferably this doesn't involve just locking the whole table every time.


texts: How can I strip non-numeric characters out of a string?

texts:
Users enter a search term in a box, and that value gets passed to a stored procedure and checked against a few different fields in the database. These fields are not always of the same data type.

One field (phone number) consists of all numbers, so when checking it strips out all non-numeric characters from the string using a .Net CLR function.

SELECT dbo.RegexReplace('(123)123-4567', '[^0-9]', '')

The problem is, this function abruptly stops working on occasion with the following error:

Msg 6533, Level 16, State 49, Line 2
AppDomain MyDBName.dbo[runtime].1575 was unloaded by escalation policy to ensure the
consistency of your application. Out of memory happened while accessing a critical resource.
System.Threading.ThreadAbortException: Exception of type
'System.Threading.ThreadAbortException' was thrown.
System.Threading.ThreadAbortException:

I've tried the suggestions posted on MSDN for this error, but am still getting the problem. At this time, switching to a 64-bit server is not an option for us.

I know restarting the server releases whatever memory it has held, but that is not a viable solution in a production environment.

Is there a way to strip non-numeric characters out of a string in SQL Server 2005 using T-SQL only?


texts: Help in understanding Hinted Handoffs and Data replication in Cassandra

texts:
We have a 3 node Cassandra cluster with RF = 2. The read and write consistency is set to ONE. We are using Vnodes as well.
Lets label these nodes as N1, N2 and N3. Let's say that N3 goes down. I was under the impression that whenever a node goes down, the other nodes would store hints and whenever N3 comes up, the hints would be sent to N3, thereby ensuring that the data is consistent across replicas. However, as I was going through the docs, I came across the parameter '' which defaults to 3 hours.
So, if a node is dead for more than 3 hours, it is considered permanently dead and no hints are stored. So far, so good.

So, my understanding now is that if a node is down for say 10 hours, then the hints for the first 3 hours would be transferred to this node when it comes back up, but the writes for this 7 hour duration would be lost for this node. Moreover, if a read query is fired for a particular token range, and since this node is also elligible to serve the read requests for a token range, it would return null instead of the actual data that is stored in some other node. Is my understanding correct? What, then, should be done?


texts: Why would I use the MySQL "system" command?

texts:
What can I do with the MySQL client's system command?  Why does it exist?

The most productive use I've seen is to look around the file system (e.g., to remember the file name you want to SOURCE or LOAD FILE).

mysql> system ls /tmp
backup.sql
mysql> source /tmp/backup.sql
Query OK, 1 row affected (0.02 sec)

mysql>

As near as I can tell, you can't pass a query result to the STDIN of a command, you can't pass STDOUT of a command into a MySQL query.. it just doesn't seem widely useful.


texts: Multiple concurrent "REFRESH MATERIALIZED VIEW": how to manage?

texts:
I have a large Postgres database with many tables, some with tens of millions of rows. Several working processes update the database concurrently. For faster searches, relevant data is compiled into a materialized view.

There are possibly multiple parallel processes writing to the database, then refreshing the materialized view afterwards. However, as the "REFRESH MATERIALIZED VIEW" query takes at least several minutes, quite often such queries pile up in a queue, and they all execute one after the other.

Unfortunately in such cases, only the latest query is of any relevance; all the previous queries consume processing time in vain to refresh stale data. Is there a way for stopping already running calls to "REFRESH MATERIALIZED VIEW" when a new call is issued?

Please note that "REFRESH MATERIALIZED VIEW CONCURRENTLY" has the same behaviour, but considerably slows the refresh (from a few minutes up to an hour), therefore aggravating the performance problem.

Of course it's possible to test for an existing lock on the view for each new query, so it's easy to cancel new queries; the problem is that I'd rather cancel the old queries and keep only the latest one...


texts:
I am in the process of designing a database for my company to assist in asset management.  I'm not a database administrator, but I had some previous course experience with databases. Our assets are used in the field at various job sites or in a warehouse or vendor facility.  My question relates to best practices for handling the case where a table can have one of two fields completed.  In my case, the asset can be at a shop or at a job.

The way I am considering handling this is adding a flag field that indicates whether the asset is on a job.  If true, then the JOB_ID column should have a value and the SHOP_ID should be null.  If false, the opposite should be the case.  Those IDs will reference the appropriate JOB and SHOP tables.

My question is, am I approaching this from the right direction?  Are there best practices for these cases?  I'm assuming tracking inventory is far from a novel problem.

Here is the schema for the ASSET table with some columns excluded for brevity's sake: ASSET(ASSET_ID, IS_ON_JOB, JOB_ID, SHOP_ID)


texts:
I'm trying to update 2 rows at once, I want to:

1) Set coupon.start (datetime) to the original account.vipExpires (datetime)

2) Add coupon.days (int) to account.vipExpires (datetime)

3) Set coupon.end (datetime) to the new value of account.vipExpires after coupon.days have been added

UPDATE accounts a, coupons c
SET c.start = a.vipExpires,
    a.vipExpires =  DATE_ADD(a.vipExpires, INTERVAL c.days DAY),
    c.end = a.vipExpires
WHERE a.id = 1 AND c.id = 1;

The result I'm getting is:

c.start, a.vipExpires, c.end ALL end up as the new datetime with the added days.

How do I make c.start be equal to the original a.vipExpires value?

I originally thought the SET operations would execute in the order specified, it seems I was wrong.


texts: Altering multiple tablespaces at once in Oracle

texts:
I need help in altering multiple tablespaces at once in Oracle Database 11g.
My particular scenario is that this command will be used in a shell script to set all of the tablespaces in read-write mode for upgrade purposes.
The problem is that each server has a different number of tablespaces, so i can't hardcode the names right in. To solve this I will make a list of all the relevant tablespaces by using the datafiles and I want to pass this list it as an argument.

For instance, you alter one tablespace as such :

ALTER TABLESPACE DATA1 READ ONLY;

I want to do something of the likes :

ALTER TABLESPACE DATA1,DATA2,DATA3 READ ONLY;

Is this possible? If so what is the exact syntax.
Thanks in advance!


texts:
I have a User Relations table, which basically stores all the one-directional relations of users.
For example , User A follows user B ; User X blocks User Y

User ids:

the id of users are bigints.
It's not like I think that using ints would really end up in a bottleneck(unless my app grows to more than 2 billion users that is :) ), but as far as a I understand, if I integrate social networks signups, Facebook uses bigints for their user ids, hence I have to keep the id of the users as bigint.

Indexing :

At first I thought about making a composite index of both user (the relating and the related), but since both indexes are bigints, meaning 8 bytes each, wouldn't it be a waste to store such a monstrous index?
I ended up leaving only one column index, but not sure whether it's the wisest decision in this case.

I also thought of a variant with covering index - but in this case the relation_type(follow, block) will not be part of indexing/filtering, so maybe I should include that inside the composite index, but then I would have 8+8+4 bytes of index.
The tradeoffs between space and time are not clear to me in this case.
Would appreciate any thoughts on the matter.

First variant (one-column index):

CREATE TABLE user_relations (
    relating_user_id bigint NOT NULL,
    related_user_id bigint NOT NULL,
    relation_type smallint NOT NULL,
    created_at default current_timestamp,

    PRIMARY KEY (relating_user_id),
    FOREIGN KEY (relation_type) references user_relations_types (id)

    -- will having only one index affect the performance really ?
    -- should I include the type into the composite type ?
);

Second variant (using covering index and omitting PK) :

    CREATE TABLE user_relations (
        relating_user_id bigint NOT NULL,
        related_user_id bigint NOT NULL,
        relation_type smallint NOT NULL,
        created_at default current_timestamp,

        FOREIGN KEY (relation_type) references user_relations_types (id),
        CREATE UNIQUE INDEX relating_user_related_user_relation_type_idx ON user_relations
                    (relating_user_id, related_user_id) INCLUDE (relation_type);

        -- should I include the type into the composite type ?
    );


texts: Modeling a Car Dealership context in an Entity-Relationship diagram

texts:
I am creating a conceptual database model for a car dealership. I have spoken to the client, gathered all the requirements, and thought I had my Entity-Relationship diagram all correct; however, I realised I have no idea how to represent the requirements that I will detail bellow.
Scenario description
The dealership offers vehicle inspections and servicing and repairs. When this happens, a service ticket is generated. A service ticket belongs to a customer, is related to a vehicle, and has the following requirements (this is the part I can't figure out how on earth to draw):

A service ticket may contain either one or more services (identified as an entity type), inspections (recognized as an entity type) and/or repairs (classified as an entity type).

Questions
Having described my context of interest:

Could someone please point me in the right direction for how to model a relationship, and how it might be maintained in a database, where a given ServiceTicket must be related to, at least, one specific Service, one particular Inspection and/or one determined Repair, yet may also contain multiple instances of these kind of entities?

How do I express that a ServiceTicket might contain zero occurrences of all three of these entity types, but has to contain at least one of them?

This problem has been doing my head in. Initially, I defined a cardinality of zero-to-many between a ServiceTicket and the three of them, but realised that such depiction does not mean it must be connected with at least one of the three. My textbook does not seem to provide any answers for this diagram and I feel that to be a good DBA I must know how to represent this.


texts: MySQL statement in Project Voldemort site

texts:
I've just entered  Project Voldemort (a NoSQL key-value datastore) site, and saw something interesting:

  "Unlike MySQL replication, both reads and writes scale horizontally"

Well, seems like this statement tries to compare Valdemort with a MySQL traditional RDBMS. Yet, MySQL Cluster is also part of the MySQL solution family. It can scale writes and reads horizontally.

Wasn't this statement a little bit unfair to MySQL?
Can MySQL Cluster scale well at all?


texts: The log for database 'tempdb' is not available

texts:
2017-07-06 05:53:02.69 spid17s     Error: 17065, Severity: 16, State: 1.
2017-07-06 05:53:02.69 spid17s     SQL Server Assertion: File: <latch.cpp>, line = 1538 Failed Assertion = '0' Attempted release of unacquired latch. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.
2017-07-06 05:53:02.69 spid17s     Using 'dbghelp.dll' version '4.0.5'
2017-07-06 05:53:02.70 spid17s     **Dump thread - spid = 17, PSS = 0x1589E7D8, EC = 0x1589E7E0
2017-07-06 05:53:02.70 spid17s     ***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump1824.txt
2017-07-06 05:53:02.70 spid17s     * *******************************************************************************
2017-07-06 05:53:02.70 spid17s     *
2017-07-06 05:53:02.70 spid17s     * BEGIN STACK DUMP:
2017-07-06 05:53:02.70 spid17s     *   07/06/17 05:53:02 spid 17
2017-07-06 05:53:02.70 spid17s     *
2017-07-06 05:53:02.70 spid17s     * Location:      latch.cpp:1538
2017-07-06 05:53:02.70 spid17s     * Expression:    0
2017-07-06 05:53:02.70 spid17s     * SPID:          17
2017-07-06 05:53:02.70 spid17s     * Process ID:    4572
2017-07-06 05:53:02.70 spid17s     * Description:   Attempted release of unacquired latch

The above is the error log details. It seems there is some issue with tempdb corruption. The instance restart fixed the issue and sql dump creation stopped.

What seems to have resolved is that tempdb gets recreated after the restart. What are your views on this?


texts:
I am working on a MySQL stored procedure.

I need to create a temp table and I want to access this temp table whenever I execute this stored procedure.

I know we can't access the temp table in another request in MySQL.

Is there a way to create a temp table like globally, or how can I access the temp table across the multiple requests?


texts: What does 'innodb_buffer_pool_reads' and 'innodb_buffer_pool_read_requests' actually mean?

texts:
Mysql version: 5.7.18 (Innodb)

I have a table with the following specification

Structure:

CREATE TABLE `dummybigint10` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT 'A wonderful serenity has taken possession of my entire soul, like these sweet mornings of spring which I enjoy with my whole heart. I am alone, and feel the charm of existence in this spot, which was created for the bliss of souls like mine. I am so.',
  `num1` bigint(20) DEFAULT NULL,
  `num2` bigint(20) DEFAULT NULL,
  `num3` bigint(20) DEFAULT NULL,
  `num4` bigint(20) DEFAULT NULL,
  `num5` bigint(20) DEFAULT NULL,
  `num6` bigint(20) DEFAULT NULL,
  `num7` bigint(20) DEFAULT NULL,
  `num8` bigint(20) DEFAULT NULL,
  `num9` bigint(20) DEFAULT NULL,
  `num10` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`pk`)
) ENGINE=InnoDB AUTO_INCREMENT=2163184 DEFAULT CHARSET=utf8

Row count: 1049088

The B+ tree of this table has 19104 leaf nodes (out of which 19075 used) and 20 internal nodes (out of which 20 is used). [Thanks to innodb_ruby project]

I fired a query to cause a full table scan as follows:

select count(*) from dummybigint10 where num9 is not null;

I observed for the values in 'innodb_buffer_pool_reads from performance_schema.

Observed values are:

innodb_buffer_pool_reads: 98

Some resource available online say that, 'innodb_buffer_pool_reads' is the number of pages which are loaded by Innodb from disk, which in my case , a full table scan on the given table has to cause at least 19104 but the actual value reported by Innodb is 98. I think that resource is misleading the interpretation of innodb_buffer_pool_reads. Can any one explain what does innodb_buffer_pool_reads actually mean?


texts: What could cause mysql-router to not open/listen to port 6446 when running as a daemon?

texts:
I have a 3 primary node innodb cluster setup which is running as expected. To connect to these I have mysql-router setup, however, when running mysql-router as a daemon service mysql-router start it does not open/listen to port 6446, 6447 or 6448. I can only get mysql-router running when running as a command line mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf
Here is a dump of mysqlrouter daemon script
. /lib/lsb/init-functions

DESC="Start / Stop MySQL Router"
DAEMON=/usr/bin/mysqlrouter
DAEMON_OPTIONS="-c /etc/mysqlrouter/mysqlrouter.conf"
NAME="MySQL Router"
RUNTIMEDIR=/var/run/mysqlrouter/
PID=${RUNTIMEDIR}/mysqlrouter.pid
STOP_RETRY=3
LOGDIR=/var/log/mysqlrouter
LOGFILE=${LOGDIR}/mysqlrouter.log
DATADIR=/var/lib/mysqlrouter

do_start() {

  local retval=0
  log_daemon_msg "Starting MySQL Router"

  if [ ! -x $DAEMON ]; then
    log_end_msg 1
    return 0
  fi

  if [ ! -d ${RUNTIMEDIR} -a ! -L ${RUNTIMEDIR} ];
  then
    install -d -m 0750 -o mysqlrouter -g adm ${RUNTIMEDIR}
  fi

  if [ ! -d ${DATADIR} -a ! -L ${DATADIR} ];
  then
    install -d -m 0750 -o mysqlrouter -g adm ${DATADIR}
  fi

  if [ ! -d ${LOGDIR} -a ! -L ${LOGDIR} ];
  then
    install -d -m 0750 -o mysqlrouter -g adm ${LOGDIR}
    install /dev/null -m 0640 -o mysqlrouter -g adm ${LOGFILE}
  fi

  [ -x /lib/init/apparmor-profile-load ] && /lib/init/apparmor-profile-load usr.bin.mysqlrouter

  start-stop-daemon --start \
    --pidfile $PID \
    --startas $DAEMON \
    --oknodo \
    --make-pidfile \
    --background \
    --chuid mysqlrouter \
    -- $DAEMON_OPTIONS 2>/dev/null
  retval=$?

  case $retval in
    0) log_end_msg 0 ;;
    1)
      log_warning_msg "already running"
      log_end_msg 0
      ;;
    2) log_end_msg 1 ;;  # failure
  esac

  return $retval
}

do_stop() {
  local retval

  log_daemon_msg "Stopping $NAME"

  if [ ! -x $DAEMON ]; then
    log_end_msg 1
    return 0
  fi

  start-stop-daemon --stop \
    --pidfile $PID \
    --startas $DAEMON \
    --quiet \
    --retry=$STOP_RETRY
  retval=$?

  case $retval in
    0)
      log_end_msg 0
      ;;
    1)
      log_warning_msg "not running"
      log_end_msg 255
      ;;
    *) log_end_msg 1 ;;
  esac

  return $retval
}

do_status() {
  status_of_proc -p $PID $DAEMON "$NAME"
}

case "$1" in
  start)
    do_start
    ;;
  status)
    do_status
    ;;
  restart|force-reload)
    do_stop
    sleep 1
    do_start
    ;;
  stop)
    do_stop
    sleep 1
    ;;
  *)
    >2& echo "Usage: /etc/init.d/mysqlrouter {start|stop|status|restart|force-reload}"
    exit 3
esac


texts: Lease Timeout VS HealthCheck Timeout - AlwaysON

texts:
After all the reading this is what I understood.  Is it correct?

LeaseTimeout is a simple heartbeat between SQL Server resource DLL and the SQL Server instance.

HealthCheck Timeout is related to sp_server_diagnostics run.

Why do we have two timeouts?

Also how do I change the LeaseTimeout?  Is it through the cluster manager because  I couldnt find any T-SQL command like below for HealthCheck Timeout

ALTER AVAILABILITY GROUP AG1 SET (HEALTH_CHECK_TIMEOUT = 40000);


texts: How do I tell what is filling up the transaction log?

texts: We have a transaction log on a sql database and for some reason it has filled up to 50GB when our live system only has about 2GB. Is there any way I can profile sql or the transaction log to try and find out why this particular one has grown so much?


texts: How to retain backups if automatic backup is enabled

texts:
Is there a way to retain the backups or configure the interval when they are deleted? According the manuals it's not possible:

  "If backup to disk is selected, the automatic backup feature will
  regularly delete backup images from the directory specified in the
  automatic database backup configuration. Only the most recent backup
  image will be available at any given time, regardless of the number of
  full backups that are specified in the automatic backup policy file."

It seems strange to me that it's not possible to retain backups.


texts: Query Spatial Data for Nearest Neighbor for each group

texts:
I want to query spatial data for nearest neighbor. I am using this article and the following query works perfectly:

SELECT TOP 1 [Location].STDistance(@Location)
FROM [DS1]
WHERE [Location].STDistance(@Location) IS NOT NULL
ORDER BY [Location].STDistance(@Location);

The issue is I need to calculate this distance for each MSPID. I have try several things (using cross apply, creating separate function, etc) but nothing worked. So, I decided to create a loop which will calculate the distance for each MSPID.

SELECT TOP 1 [Location].STDistance(@Location)
FROM [DS1]
WHERE [Location].STDistance(@Location) IS NOT NULL
    AND @CurrentMSPID = [MSPID]
ORDER BY [Location].STDistance(@Location);

The problem is that the above statement does not use the index. If I replace the @CurrentMSPID with a number the index is used, but when a variable is used, the clustered index is used instead the spatial one.

I have try a lot of options and the only one that works is:

OPTION (OPTIMIZE FOR ( @CurrentMSPID  = 1001 ))

or when the value is hard-coded. I cannot leave it that way because in the future this id could even not exists and it is sure that the number of rows which it matches will be changed.

The [DS1] table has primary key on [MSPID] column and spatial index on the [Location] column.

How can I help the engine to generate better execution plan and use the spatial index without using the OPTIMIZE FOR option?

It seems when other data is extracted from the table (we are filtering by MSPID now) the engine is not able to use the spatial index and performs clustered index seek instead.


texts: Resource Limits when switching from rsync to xtrabackup-v2

texts:
I am having some problem when using wsrep_sst_method=xtrabackup-v2
When I use node 2 join into cluster, node 2 always response "Resource limits" although I have free disk, ram and cpu.


texts: After streaming replication has failed, how to get it back again?

texts:
I have a similar problem to this: Replication has failed; how to get going on again?

Essentially my slave failed, and now complains "requested WAL segment 0000000100000135000000E4 has already been removed"

In my case I HAVE done a full base backup again as per the instructions http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial#Binary_Replication_in_6_Steps. So I shut the master down, did a full rsync, started up the slave, then started the master.  And I still get the same error.


texts: mysql crashed, after start again, the new data lost, only old records of some day ago

texts:
I got a server restart, the mysql server seems crashed.
After reboot, mysql database filled of old data for bug tracker.

Do you know how to fix this?
mysql error log as below

 131027 15:29:19 [Note] Plugin 'FEDERATED' is disabled.
 131027 15:29:19 InnoDB: The InnoDB memory heap is disabled
 131027 15:29:19 InnoDB: Mutexes and rw_locks use GCC atomic builtins
 131027 15:29:19 InnoDB: Compressed tables use zlib 1.2.7
 131027 15:29:19 InnoDB: Using Linux native AIO
 131027 15:29:19 InnoDB: Initializing buffer pool, size = 128.0M
 131027 15:29:19 InnoDB: Completed initialization of buffer pool
 131027 15:29:19 InnoDB: highest supported file format is Barracuda.
 InnoDB: The log sequence number in ibdata files does not match
 InnoDB: the log sequence number in the ib_logfiles!
 131027 15:29:19  InnoDB: Database was not shut down normally!
 InnoDB: Starting crash recovery.
 InnoDB: Reading tablespace information from the .ibd files...
 InnoDB: Restoring possible half-written data pages from the doublewrite
 InnoDB: buffer...
 131027 15:29:24  InnoDB: Waiting for the background threads to start
 131027 15:29:25 InnoDB: 5.5.32 started; log sequence number 320322004
 131027 15:29:25 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
 131027 15:29:25 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
 131027 15:29:25 [Note] Server socket created on IP: '127.0.0.1'.
 131027 15:29:26 [Note] Event Scheduler: Loaded 0 events
 131027 15:29:26 [Note] /usr/sbin/mysqld: ready for connections.
 Version: '5.5.32-0ubuntu0.13.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
 131027 15:29:34 [ERROR] /usr/sbin/mysqld: Table './bugtracker/mantis_bug_history_table' is marked as crashed and should be repaired
 131027 15:29:34 [Warning] Checking table:   './bugtracker/mantis_bug_history_table'
 131027 15:29:34 [ERROR] Got an error from unknown thread, /build/buildd/mysql-5.5-5.5.32/storage/myisam/ha_myisam.cc:912
 131027 15:29:34 [Warning] Recovering table: './bugtracker/mantis_bug_history_table'


texts: How do I log non-SSL connections to MySQL 5.5?

texts:
I've recently converted my applications to connect to MySQL 5.5 using SSL. However, I'm afraid that I've missed something, so I'm also allowing non-SSL connections to the server.

How can I log incoming connections to flag any that are connecting using the old, non-SSL method?


texts: Cannot Restore Database in SQL Server 2014

texts:
I have created a backup of a SQL database in SQL 2014 and want to use this backup to restore it to a different machine that also runs SQL 2014, hence no version issues. However, I keep receiving an error that the system cannot find the .mdf file.
Once I have selected the backup file I receive the  check mark that the database is ready to be restored but as soon as I click Ok the error appears.
Error message below; the provided link to MSFT does not contain any information.

  TITLE: Microsoft SQL Server Management Studio Restore of database
  'BEWEBAPP' failed.
  (Microsoft.SqlServer.Management.RelationalEngineTasks) ADDITIONAL
  INFORMATION: System.Data.SqlClient.SqlError: Directory lookup for the
  file "C:\Program Files\Microsoft SQL
  Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\BEWEBAPP.mdf" failed with the
  operating system error 3(The system cannot find the path specified.).
  (Microsoft.SqlServer.SmoExtended) For help, click:
  http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=12.0.2000.8+((SQL14_RTM).140220-1752)&LinkId=20476

Does this means that a backup can only be restored to the machine it was taken from??
Frankly, I do not understand why restoring a database has to be so difficult.

Any solution for this problem are well appreciated.