texts: Correct way of saving information to a database

texts:
I am making a website where people can register/login and upload files (text only) and other people can view them. I have the file upload feature which upload the file to a folder and it automatically saves file information(title, size, uploader etc.) to the database.

Is this the right way to save files using a database? And how would I add keywords to the file type, so I can use it to search by keyword.


texts: Hierarchical structure with limited number of records per user

texts:
I am planning schema for an affiliate network. For all hierarchical queries I am using Postgres tablefunc extension but here is another problem.
Any user can refer only 3 other users at max.
So for example I have this relation of users:

CREATE TABLE users
(
  id serial NOT NULL,
  referred_by integer NOT NULL,
  created_at time without time zone NOT NULL,
  updated_at time without time zone NOT NULL,
  CONSTRAINT primary_key PRIMARY KEY (id),
  CONSTRAINT referred_foreign FOREIGN KEY (referred_by)
      REFERENCES users (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
)

I can use connectby function to query any hierarchy. But when it comes to inserting related models I have to check if a user has not already referred 3 other users before inserting any new user under him. If he already have 3 users then it need to cascade down the tree and put it as a leaf node of tree which is further a heavy query to run on larger hierarchies.

For cascading new users to leaf node Is it possible to determine the nearest node where new user can be put ? for example If this is the data in table:

                    ____________ A ___________
                   /             |            \
                __ B __       __ C __     ____ D ____
               /   |   \     /   |   \   /     |     \
              E    F    G    H   I   J   K     L

And A refers one more user, Now, since A's downline is fully saturated any new user under him should cascade down the tree, and there are 9 places where the new user can be put.

E    F    G    H    I    J    K    L    D

out of which the nearest node is D, so incoming node should go under D instead of E,F..K,L

I have 3 questions:

Most important, Is it possible to restrict the number of records under each user ? I dont want to rely on triggers to check for number of records and cascade down if required. I can change the design if necessary.
How can I 'efficiently' fetch the nearest leaf node of any tree after determining that the root node is saturated ? By efficiently I mean that the same query might need to run a couple hundred times per minute and the data set has no depth limit. Again, I am open for design suggestions if any.
Is it possible to create partial index for referred_foreign so that the first user who will not be referred by anyone can still satisfy the foreign integrity ?

Is there any better design or something that I am missing ? (I know I am missing a lot just dont know what it is)


texts: Using Dynamic Queries Across Multiple Databases to Change Views

texts:
I am seeking to run a query to modify views across multiple databases (See the initial question here.) and was able to find how to get it.  I've now run into the problem that if I run this from master (Where I'm sure it has to run from) i receive null results.  However, if i go to a specific database and run the dynamic query there, i get the views i'm looking to modify (the intended result).

This is the action I want performed on each database:

select @strResult = definition from sys.sql_modules where object_id = object_id('view')

What do i need to do to ensure that I get this information back?


texts: Meeting with DBAs regarding server upgrade project - Common concerns

texts: I'm more of a network/windows admin and I've been tasked with overseeing a SQL server upgrade project. I need to meet with the DBAs and discuss their needs / wants regarding the upgrade. I don't want to go in totally blind, so I thought I would ask you guys first. We are moving from SQL Server 2008 to SQL Server 2008 R2 and likely moving to Windows Server 2008 R2 where possible. As a DBA, what would be your concerns with such an upgrade? Anything you'd like to see happen at the same time?


texts: Builds and deployments to 300+ databases utilizing SSDT/VSTS

texts:
I have built a database project within SSDT for Visual Studio 2017. I have also created Publish profiles that succesfully deploy to our dev, QA, staging, and production databases with no issue, including proper Pre/PostDeployment scripts.

The issue that I now face is that I need to "dynamically" publish the database project to 300+ databases. By dynamically, I mean that each of the 300+ databases are relatively out of sync. For instance, I have objects that I want to drop in database ABC that might not necessarily be in XYZ. I'll also have objects in XYZ that I want to drop that aren't in ABC.

The objects I want to keep are all in source control, so I am using the Publish option to Drop objects in target but not in source. There might be table definitions that slightly differ from one database to the next, especially with regards to indexes and constraints, or temporary backup tables that were created during account specific investigations that should be dropped.

Because of these inconsistencies across the schemas, an approach to build the project once and deploy that build script to all databases won't work as it might miss objects that need to be dropped or recreated in database XYZ. The goal is to essentially sync all of these databases to the same schema.

I have read a few articles that delve into utilizing batch scripts, but they all seem to cover the case of building the database build script once and deploying that single script across all databases.

I have not yet found a method that will build and publish the project for each of the 300+ databases individually. I understand that this is likely to be a rather long deployment process, but given the inconsistencies throughout all the schemas due to mixed deployment procedures prior to my coming on with the company, we need to get the schema to a state that is consistent across the board.

We are using Visual Studio Team Services for our TFS repository, so I believe there may also be an option to create a Build Definition workflow in this scope. But alas, there seems to be relatively minimal information on this use case. PowerShell calls to SQLCMD might also be another option, but I have little to zero experience with this.


texts: TSQL (2014) - Importing XML with accented and punctuation Characters

texts:
Using this solution from Forrest:

DECLARE @XML xml =
'<?xml version="1.0" encoding="UTF-8"?>
   <Orders>
    <Order>
        <OrderID>334</OrderID>
        <AmountPaid currencyID="EUR">17.10</AmountPaid>
        <UserID>marc58</UserID>
        <ShippingAddress>
            <Name>Marc Juppé</Name>
            <Address>Rue  garçonneé III° arrondissement</Address>
            <City>Paris</City>
            <StateOrProvince></StateOrProvince>
            <Country>FR</Country>
            <Phone>333333333</Phone>
            <PostalCode>22222</PostalCode>
         </ShippingAddress>
         <ShippingCosts>4.50</ShippingCosts>
         <Items>
            <Item>
               <Details>
                    <ItemID>3664</ItemID>
                    <Store>47</Store>
                    <Title>MCPU DDA010</Title>
                    <SKU>mmx</SKU>
                </Details>
                <Quantity>1</Quantity>
                <Price currencyID="EUR">6.2</Price>
            </Item>
            <Item>
               <Details>
                    <ItemID>3665</ItemID>
                    <Store>45</Store>
                    <Title>MCPU DFZ42</Title>
                    <SKU>mmy</SKU>
                </Details>
                <Quantity>2</Quantity>
                <Price currencyID="EUR">3.2</Price>
            </Item>
        </Items>
    </Order>
</Orders>'

SELECT
    x.value('./ItemID[1]','int') AS ItemID,
    x.value('./Store[1]','int') AS Store,
    x.value('./Title[1]','nvarchar(100)') AS Title,
    x.value('./SKU[1]','nvarchar(100)') AS SKU,
    x.value('../Quantity[1]','int') AS Qty,
    x.value('../Price[1]','decimal(11,2)') AS Price,
    x.query('//OrderID[1]').value('.','int') AS OrderID,
    x.query('//AmountPaid[1]').value('.','decimal(11,2)') AS AmountPaid,
    x.query('//UserID[1]').value('.','nvarchar(100)') AS UserID,
    x.query('//ShippingCosts[1]').value('.','decimal(11,2)') AS ShippingCosts
FROM @XML.nodes('//Item/Details') i(x)

I receive an error like

  XML parsing: line 162, character 34, illegal xml character

when need to parse fields containing accented characters (àèòìùé) or °

I tried converting é int &egrave; but get this error:

  XML parsing: line 162, character 41, well formed check: undeclared entity

while if I convert to &#232; it works.

The problem is I cannot HTMLEncode the XML File since I expect it will convert also all the "<" and ">" of the XML Structure, and probably also something else.

Can suggest the best solution to HTMLEncode the XML before parsing?


texts: Preferred Methods of copying a new database from Development to Production

texts:
I am wondering what are preferred methods to copy a new (Development) database into a new environment.
The database will be empty, so essentially I am copying only the schema of this database and not the data.

Options include:
1) SSMS (Backup/Restore). I do not use this one because it will copy test data into Production.
2) SSMS (Generate Scripts)
3) Writing T-SQL myself
4) RedGate (or other 3rd party tools out there)

Does anyone have preferences or biases?

For myself, I use most (4): I create a new empty database using SSMS, then populate the schema of new database using RedGate.

Perhaps, it makes no difference. But I would like to know from the more experienced DBAs what their opinions are. Thank you.


texts: Login oracle database with Microsoft Active Directory error

texts:
I have two machines, one (let's call it dbmachine) has the database and client installed of oracle 11g release 2, the other one is the domain controller (let's call it dcmachine) and has the client installed, same version. They are in the same network and both can communicates with each other, usign ip or hostname.
I've been trying to configure the database to be able to log in with users that are in the active directory but it always says that username or password are incorrect. I followed this instructions
On the dcmachine the oracle context has created successfully, the net service is able to connect with dbmachine:

On dbmachine I was able to create a user to the database using one that already exist on the AD, fdb in this case:

The tsnames.ora file contains the following code (in both machines):
ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracleDDBB.oracle.internal.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

The ldap.ora file contains(in both machines):
DEFAULT_ADMIN_CONTEXT = "DC=oracle,DC=internal,DC=com"

DIRECTORY_SERVERS = (DCOracle.oracle.internal.com:389:636) #This line only exist on dbmachine

DIRECTORY_SERVER_TYPE = AD

Finally the sqlnet.ora file contains (in both machines):
SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT, LDAP)

NAMES:LDAP_AUTHENTICATE_BIND=TRUE #This line only exist on dbmachine

I'm trying to connect to the database using:
connect "OPS$ORACLE\FDB"@orcl

The error is the following one:

I've been stuck here for days, the only difference that I notice between my configuration and the  instructions is that in the instructions the database service appears in the active directory. I don't know how to create it:


texts: What is this view being used in my query?

texts:
I'm running EXPLAIN PLAN on this query:

SELECT DISTINCT l.item_num, l.repl_code
FROM item_w l
JOIN item i ON l.item_num = i.item_num
LEFT JOIN item_w r ON l.item_num = r.item_num AND l.repl_code = r.whse_code
WHERE r.whse_code IS NULL
  AND l.repl_code IN (SELECT DISTINCT whse_code FROM item_w)
  AND i.sa_item NOT LIKE 'Z%'
ORDER BY l.item_num, l.repl_code

The query works as desired but the plan is telling me that it is using objects named VW_DTP_F6D0ECAD, VW_DTP_53909802 and VW_DTP_69531A7E which I'm not able to find in my database.

What are these objects and how are they being created?


texts:
I have a few questions regarding the setup of Always On HA, and Clusters.

When you setup an AG, and it gets to setting up your IP address for the cluster how can I switch from using DHCP to a static IP address? I have it working with DHCP right now but in the future I would like to change that for my existing AG and also for future groups I setup. It does not seem like I'm being given the option in my setup though.

The second question is when setting up the group to use full sync it works just fine. However what I am trying to figure out is where that sync data is stored. I am using Synchronous-commit mode. From what it looks like the initial sync, and the data that is mirrored is stored and applied in a basic log I just cannot seem to find where that log is stored.

Any information or links would be greatly appreciated.


texts: In Oracle, how to globally enable parallel execution?

texts: In Oracle 11g, is there a way to tell the database to use parallel execution whenever possible by setting a flag etc, rather than using PARALLEL hints with every SQL statement?


texts: Where would the table data be if you had 3 filegroups, 2 with tables and 1 with all indexes?

texts:
I'm studying filegroups at the moment as I haven't used them much before. A class I'm taking mentions the above scenario. I was wondering if I'm understanding it correctly:

Would the 2 filegroups with tables just be holding table objects and the 1 filegroup with indexes be holding the table data?


texts: MySQL query conditional return using TRIM()

texts:
I am trying to SELECT from a table, but I need to trim from leading and trailing string for comparison. For example,

My table/data is very simple

CREATE TABLE `lcr` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `prefix` varchar(45) DEFAULT NULL,
  `digits` varchar(45) DEFAULT NULL,
  `price` decimal(20,4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

mysql> select * from lcr;
+----+--------+--------+--------+
| id | prefix | digits | price  |
+----+--------+--------+--------+
|  1 | 1122   | 92300  | 0.0198 |
+----+--------+--------+--------+
1 row in set (0.00 sec)

I am running the following select

SELECT * FROM `lcr` WHERE `digits` IN ((SELECT (TRIM(LEADING '1122' FROM '112292300'))), (SELECT (TRIM(LEADING '1122' FROM '11229230'))), (SELECT (TRIM(LEADING '1122' FROM '1122923'))), (SELECT (TRIM(LEADING '1122' FROM '112292'))))

which trims the leading 1122 from digits, and gives a positive match. But when I run the query

SELECT * FROM `lcr` WHERE `digits` IN ((SELECT (TRIM(LEADING '1122' FROM '92300'))), (SELECT (TRIM(LEADING '1122' FROM '9230'))), (SELECT (TRIM(LEADING '1122' FROM '923'))), (SELECT (TRIM(LEADING '1122' FROM '92'))))

I also get a positive match. Because Trim is actually not doing anything and in my table, digits are 92300.

Is there a way we can use some sort of condition or if function or something like that which should only return the result if prefix+trim = actual data passed?

I may not be much expressive asking my question here, so apologize for that and let me know if there is any info required to answer my question.

Thanks.


texts: How to copy a table in a production server without WRITE lock and without writing to a file

texts:
I'm trying to copy a table in a production table. When I'm performing on InnoDB tables:

INSERT INTO new_table (SELECT * FROM table)

I have a write lock on table. Is it possible to avoid this lock? I am aware there is a solution to write to a file, but due to disk space limitation, I would have to pass on that solution. Also, are there any flags I might be able to add that will make it possible to kill the query during execution without performing rollback?


texts: Transfer Data between MySQL and MongoDB Databases

texts:
My latest IT project has me working with MongoDB for the first time.  While I have a bit (very little) experience with NoSQL, I have always mostly worked with MySQL using Workbench.

I need to migrate the data from a MySQL database into MongoDB as my employer is switching over from the former to the latter.

Can someone suggest what might be the easiest way to do that?

Thanks a bundle!


texts: SQLAnywhere: Failure on creation of procedure

texts:
I wanted to create a procedure like this:

CREATE OR REPLACE PROCEDURE __generate_new_VSN(@oldVSN varchar(100))
RETURNS VARCHAR(100)
AS
BEGIN
    RETURN '1'
END

but getting when running it via isql:

Anweisung konnte nicht ausgeführt werden.
Syntaxfehler bei 'RETURNS' in Zeile 2
SQLCODE=-131, ODBC 3-Status="42000"
Zeile 1, Spalte 1

which can be translated to something like

Command couldn't be executed
Syntax error at 'RETURNS' on line 2
SQLCODE=-131, ODBC 3-Status="42000"
Zeile 1, Spalte 1

What did I wrong?


texts: What should be checked after an Oracle migration

texts:
After a large data migration, what needs to be checked to be confident that the data move has succeeded?

the number of tables and rows in each schema is the same.
the text data in the tables has not been corrupted. This is important if non alphanumeric characters (such as double byte) are used.
the dumps or other files that have been been transferred have the same checksum.

Is there anything else that can be checked?


texts: Encountered Error: ORA-00979: not a GROUP BY expression 00979. 00000 - "not a GROUP BY expression"

texts:
I want to join 3 table together and display the output as below.But i encountered the error when i tried to use group by and order by together. Is there any solution can solve this error? Below is my Oracle query. Appreciate if someone could help on this error. Thank you.

The database data is like below:
This is my 1st table (RMSTMP_PNG.ota_activity_lotinfo):

       ID                           KEY               VALUE
AD9FBCCC49F06446E050280A2         Quantity            100
AD9FBCCC49F06446E050280A2        PackageLeads         BN1140
AD9FBCCC49F06446E050280A2        TestProgram          DL_FP5_SLT
AD9FBCCC49F06446E050280A2          TestRev            3.0.0
AD9FBCCC49F06446E050280A2           IDV               9651AA43

Second Table as below (RMSTMP_PNG.ota_activity):
EQP_ID  LOT_ID   ACTIVITY  MODIFIED BY  MODIFIED DATE              ID
LAS43   9KA2909  LOAD_LOT  jratnasa     18-AUG-20       AD9FBCCC49F06446E050280A2

Third Table (RMSTMP_PNG.ota_activity_rescreen)
       ID                        RESCREEN TYPE
AD9FBCCC49F06446E050280A2           FRESH

The output i want is group all same ID as below and display in gridview but i am not sure is it possible to do that?

The final output should be like this:
EQP_ID   MODIFIED DATE         ID                            name
LAS43    18-AUG-20       AD9FBCCC49F06446E050280A2        Quantity:100,
                                                          PackageLeads:BN1140,
                                                          TestProgram:DL_FP5_SLT,
                                                          TestRev:3.0.0,
                                                          IDV:9651AA43

i come up with this query, but seem like it does not working.

SELECT RMSTMP_PNG.ota_activity.EQP_ID,
       RMSTMP_PNG.ota_activity.MODIFIED_DATE,
       RMSTMP_PNG.ota_activity_lotinfo.ID,
       LISTAGG(RMSTMP_PNG.ota_activity_lotinfo.KEY)
              WITHIN GROUP (ORDER BY RMSTMP_PNG.ota_activity_lotinfo.KEY) as "names"
from RMSTMP_PNG.ota_activity
INNER JOIN RMSTMP_PNG.ota_activity_rescreen
    ON RMSTMP_PNG.ota_activity.ID = RMSTMP_PNG.ota_activity_rescreen.ID
INNER JOIN RMSTMP_PNG.ota_activity_lotinfo
    ON RMSTMP_PNG.ota_activity.ID = RMSTMP_PNG.ota_activity_lotinfo.ID
WHERE RMSTMP_PNG.ota_activity.MODIFIED_DATE BETWEEN'01-Jan-2020'AND '30-Sep-2020'
GROUP BY RMSTMP_PNG.ota_activity_lotinfo.ID,
         RMSTMP_PNG.ota_activity_lotinfo.KEY,
         RMSTMP_PNG.ota_activity.EQP_ID
order by RMSTMP_PNG.ota_activity.MODIFIED_DATE ASC


texts: MATLAB® export double numbers into PostgreSQL by `datainsert` (of Database Toolbox)

texts:
How is it possible to pass double numers into numeric postgreSQL columns from MATLAB?

Here the list of matlab data:

var1    197x1 cell
var2    197x1 double
var3    197x2 double
var4    197x2 double
var5    197x2 double

Here the list of postgreSQL columns:

col1        text    NOT NULL ,
col2        numeric(10,4),
col3        numeric(20,4),
col4        numeric(30,20),
col5        numeric(30,20),
col6        numeric(20,4),
col7        numeric(30,20),
col8        numeric(30,20),

Here the code (from script.m) I used:

for r=[1:n]
    colnames={'col1','col2','col3','col4','col5','col6','col7','col8'};
    data={char(var1(r)),var2(r,1),var3(r,1),var4(r,1),var5(r,1),var6(r,2),var7(r,2),var8(r,2)};
    datainsert(conn,tablename,colnames,data);
end

This code passes only numbers before the comma.


texts: MySQL won't start, no log ins /var/logs/mysql*

texts:
MySQL sudently stop working, when start manually mysqld with `mysqld`` command:

# mysqld
160805 11:34:10 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
160805 11:34:10 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
160805 11:34:10 [Note] Plugin 'FEDERATED' is disabled.
160805 11:34:10 InnoDB: The InnoDB memory heap is disabled
160805 11:34:10 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160805 11:34:10 InnoDB: Compressed tables use zlib 1.2.7
160805 11:34:10 InnoDB: Using Linux native AIO
160805 11:34:10 InnoDB: Initializing buffer pool, size = 128.0M
160805 11:34:10 InnoDB: Completed initialization of buffer pool
160805 11:34:10 InnoDB: highest supported file format is Barracuda.
InnoDB: Error: trying to access page number 46616 in space 0,
InnoDB: space name ./ibdata1,
InnoDB: which is outside the tablespace bounds.
InnoDB: Byte offset 0, len 16384, i/o type 10.
InnoDB: If you get this error at mysqld startup, please check that
InnoDB: your my.cnf matches the ibdata files that you have in the
InnoDB: MySQL server.
160805 11:34:10  InnoDB: Assertion failure in thread 140324139591456 in file fil0fil.c line 4578
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
09:34:10 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

key_buffer_size=16777216
read_buffer_size=131072
max_used_connections=0
max_threads=151
thread_count=0
connection_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 346700 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0 thread_stack 0x30000
mysqld(my_print_stacktrace+0x29)[0x7f9fc2d3f0b9]
mysqld(handle_fatal_signal+0x3d8)[0x7f9fc2c26418]
/lib/x86_64-linux-gnu/libpthread.so.0(+0xf0a0)[0x7f9fc23d40a0]
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0x35)[0x7f9fc0c65165]
/lib/x86_64-linux-gnu/libc.so.6(abort+0x180)[0x7f9fc0c683e0]
mysqld(+0x5f3333)[0x7f9fc2df6333]
mysqld(+0x5cee49)[0x7f9fc2dd1e49]
mysqld(+0x5cf7ec)[0x7f9fc2dd27ec]
mysqld(+0x5c02c8)[0x7f9fc2dc32c8]
mysqld(+0x59d55f)[0x7f9fc2da055f]
mysqld(+0x592d44)[0x7f9fc2d95d44]
mysqld(+0x593aac)[0x7f9fc2d96aac]
mysqld(+0x59541a)[0x7f9fc2d9841a]
mysqld(+0x582b23)[0x7f9fc2d85b23]
mysqld(+0x54f0ff)[0x7f9fc2d520ff]
mysqld(_Z24ha_initialize_handlertonP13st_plugin_int+0x41)[0x7f9fc2c287a1]
mysqld(+0x3329d7)[0x7f9fc2b359d7]
mysqld(_Z11plugin_initPiPPci+0xa73)[0x7f9fc2b38a53]
mysqld(+0x2b9155)[0x7f9fc2abc155]
mysqld(_Z11mysqld_mainiPPc+0x45b)[0x7f9fc2abcdcb]
/lib/x86_64-linux-gnu/libc.so.6(__libc_start_main+0xfd)[0x7f9fc0c51ead]
mysqld(+0x2b0f59)[0x7f9fc2ab3f59]
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

Tryied in my.cnf

[mysqld]
innodb_force_recovery = 1

without change. Where can i investiguate ?


texts: Default value for CHARSET when creating a table

texts:
I came across this query in a book i'm learning from.
  CREATE TABLE `articles` (
      `title` varchar(1000),
      `content` text,
      `links` text,
      `image` varchar(500),
      `url` varchar(500),
      UNIQUE KEY `url` (`url`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8

I want to understand the last line of this query, in particular "DEFAULT CHARSET=utf8"
I looked up the documentation on table options

[DEFAULT] CHARACTER SET
Specifies a default character set for the table. CHARSET is a synonym for CHARACTER SET. If the character set name is DEFAULT, the database character set is used.

From the first line of this explanation, "[DEFAULT] CHARACTER SET" it looks to me as if this actually setting "CHARACTER SET" to default for all subsequent table creations, as well as this current one?
in the case of the code from the book, database character set is different to utf8, which would be used? If it's utf8 still, why have they put DEFAULT there anyway?
Is the last line of the documentation above talking about this?
CREATE TABLE `articles` (
      `title` varchar(1000),
      `content` text,
      `links` text,
      `image` varchar(500),
      `url` varchar(500),
      UNIQUE KEY `url` (`url`)
    ) ENGINE=InnoDB DEFAULT CHARSET=DEFAULT

thanks.


texts: selecting multiple instances of a record based on record lifespan over years

texts:
I have a table of items that have a origin date and a life span. I want to create a procedure that selects all of the items that will need to be replaced in a specific duration. I feel like there is a math way of doing this in the where clause of a single select. The only way i can think of doing it is iterating though the years and appending the items that would be up for replacement... that seems super slow and inefficient. How should I approach this issue?

Details per comment requests:

Database Engine: SQL Server 2005

Source Table:

CREATE TABLE Items
(
    ID int,
    ItemName varchar(255),
    InstallYear int,
    UsefullLife int
);

Desired stored procedure format:

GetReplacementsWithinYearRange(startyear int, endyear int)

Desired output:

ItemID - ItemName - ReplaceYear

More details:

Sample Records:

1  item1  2010  5
2  item2  2011  6

Desired results for range between 2010 and 2030:

1  item1  2010
1  item1  2015
1  item1  2020
1  item1  2025
1  item1  2030
2  item2  2011
2  item2  2017
2  item2  2023
2  item2  2029


texts: Intermittent Disconnection to DB on SAN. Event ID 17053 and 9001

texts: I am getting some strange issues with our MS SQL 2008 database where intermittently we lose connection to the db for 10+ seconds or so.  The db is stored on a SAN over fiber.  The latest event log error I get is "LogWriter: Operating System error 1 (incorrect function) encountered with an event ID of 17053.  Following that error is event ID 9001 about log for database is not available.  I would then see a message stating recovery is completed for database in 12 seconds.  Not sure how to approach this and any help or guidance would be much appreciated.  Running MS SQL 2008 on Windows 2008 SP1.


texts:
Background:

I sought to rename a database on Dreamhost web hosting VPS account.
I also wanted to rename a database user for the database to be created above.
I followed the approached detailed at ServerFault
I used mysqldump to create a backup of the old database (<1MB) as a *.sql file.
I created a new database with a desired name within the DreamHost CPanel.
I created a new database user within the DreamHost CPanel (not via the linux command console) to have access to the new database.
I imported (via linux command console) the *.sql (from mysqldump) as the new desired database.
I did not drop the old database - as I wanted to be sure that everything was alright before I drop the old database (and edit related scripts pointing to that old database).

Yet upon checking my DreamHost CPanel,

the size of the new database was not recognised.
the new database user had access to the new database as expected (this was tested via phpMyAdmin).

Furthermore

As expected phpMyAdmin does not show me the new database when I logged in with old user details.
As expected phpMyAdmin recognises the new database when I logged in with the new user details.
phpMyAdmin recognises the size of the new database.
phpMyAdmin shows that there is difference in size between the old and new database (I thought this is supposed to be the same size since its a backup).
I briefly checked the database structure (in phpMyAdmin) and noticed that the difference is coming from the table property “column” known as “Overhead”.
The Old database has Overheads in a few tables while the mysqldump file does not have any overheads.

Question:

How come the difference in size
Home come the DreamHost CPanel reports that size is unavailable
What does Overhead even mean

Any help would be appreciated


texts: Backing up SQL Server Express Edition on a VPS

texts:
I'm considering installing SQL Server Express Edition on a VPS.

My understanding is that, among the limitations of Express Edition, some of them involve features that facilitate backups.

Can someone explain some options for performing daily backups for this configuration. Is there a straight forward way, or do I need to buy additional software?

(Sorry if the question seems dumb. I'm a software developer and not at all a database or admin guy.)


texts: SQL Trigger that creates new table when a new record is added to current table

texts:
It's been quite a few years since I have done DB work.  I am working through mysql workbench (using MS Access as a frontend).

I have a base table that I am multilinking (basically a name index type of thing). Through a form, the table is updated (a record added) and I want that new record to trigger an event that creates a table and that table is named based on what the user inputted in the form/record.

Any thoughts on how to do this?  Good tutorial/documentation that you would recommend reading?
Thanks!


texts: understanding mysqldump auto-backup performance issues and inner functionality

texts:
Is there a performance issue, if I run a cronjob in background with mysqldump --single-transaction with a 60 seconds interval? Will my original database keep functioning without an issue while the dumping is in progress?

what if I reduce the time interval to 5 seconds (in order to have a real time update), does this affect the performance of my database server a lot?

Also how does the mysqldump command generate the sql file? ( does it generate the statements from scratch again and again as we execute the command, or it just has the statements generated ready, and just gives it when we execute the mysqldump command? )


texts: SQL Parallel Stored Procedure execution using CLR - Performance

texts:
We have a main analytics SP that calls 100 other sub SPs, all of which act upon the same set of data (Claims), check it for some business rules, and output discrepancies.

The SPs need not all be run sequentially, they can be broken out into sections, which are dependent on previous sections (sequential), but independent within the section (parallel).

After looking at many options like Service Broker, Agent Jobs, Batch Files, SSIS etc., I used this CLR code to parallelize the sections, and it gave great performance improvement.

However, when I run multiple (5, 10, 15) main SPs concurrently (each of which analyzes different claims) , performance starts to taper as concurrency increases. I guess this is because of the overhead of creating multiple parallel threads through the CLR. I also see lot of XTP_THREAD_POOL sessions idle in sp_who2.

Has anyone used CLR for parallelizing Stored Procedures in Critical OLTP Production workloads ?
Are there any best practices for performance tuning SQL CLR ?
Is there a threshold for number of parallel threads that can be opened before the overhead makes things worse ?
If my system has 20 cores, does it mean creating > 20 parallel threads does not help ?


texts: Normalizing a database without access to source data?

texts:
I've started a new role which deals with large amount of related data. Our source of all this data is various Excel dumps pulled from databases we do not have access to. The previous person filling this role used a dozen or so Excel files to gather these data files, manipulate them, and create reports.

I have started moving the dumps to an Access database. I have noticed a lot of the Excel data is related and should probably be normalized. What I have currently been doing is creating a table for every data dump, and importing them to Access, and using a number of queries to replicate the dozens of data manipulations and reporting.

Are there still benefits to normalizing data where my only source are Excel dumps out of a warehouse?

How would I normalize data, when I do not have the ability to change the format of how the dumps are sent to me?

Additionally, my plan (budget-depending) is to move from Access to a MS SQL Database.


texts: Security best practices for SSIS package execution via SQL Agent job

texts:
We're trying to rein in the number of sysadmins on our servers, and one of the issues we're running into pertains to SQL Agent security -- especially with regard to SSIS jobs and our ETL team. I understand that non-sysadmins cannot execute SSIS job steps by default and that proxies can be created in order to allow this, but I'm still struggling with the implementation.
This post talks about using proxies to allow non-sysadmins to manage and maintain SQL jobs, but creating credentials for each of my ETL developers and maintaining those via screen-share every time their password changes (according to our AD policy) seems clumsy at best. Even with just a handful of developers this quickly becomes untenable. I can't afford to have jobs fail because someone's password expired, and I'd rather not rely on people remembering that the credential needs to be updated every time their password changes.
I'm curious how other shops deal with this. We can't be the only company with a team of ETL developers that need to be able to create and manage SQL Agent jobs to run the SSIS packages they develop. Do you use a service account for the credential/proxy and grant everyone on the ETL team access to the proxy? And if so, does this same service account "own" the SQL Agent job or do the individual developers?
It would be nice if anyone on the team could make changes to jobs as needed, but it seems the only way to accomplish this is to use a "shared" service account or make everyone sysadmins -- which is exactly the scenario we're trying to avoid. What are the best practices? Can anyone speak to their own implementation? I'm trying to understand how I can use credentials and proxies to help, but I  haven't been able to find much concrete guidance with specific implementation instructions or best practices.
Apologies if my question was all over the place. Here's the scenario: An SSIS developer deploys a package and creates a SQL Agent job to run that package. By default, he's the owner of the job. Because he's not a sysadmin, however, the job throws this error: Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account.  The step failed. If I make the sa account the owner of the job, it executes but the SSIS developer can no longer make changes to it. Even if I grant ssis_admin in SSISDB and db_ssisadmin in msdb the job will fail.
I want to have my cake and eat it too. Obviously the job needs to run, and I would prefer not to involve a sysadmin every time a job needs edited. The closest I've come to being able to do this is to create a credential for the developer, associate a proxy with that credential, then give the proxy access to the SSIS Package Execution subsystem. But then I'm stuck maintaining passwords for each of the credentials every time they change. And if I should ever miss updating a credential password when it expires or is changed, the job will begin failing again.

The ETL dev owns the job;
The ETL dev belongs to SQLAgentOperatorRole in msdb (which allows him to make changes to his own jobs but not those owned by others);
The ETL dev belongs to db_ssisadmin, db_ssisltduser, and db_ssisoperator in msdb;
The ETL dev belongs to ssis_admin in SSISDB Even then, and even if I'm the one executing the job (as a sysadmin), it fails with Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account.  The step failed.

The job will only succeed if it's owned by a sysadmin or if the SSIS step executes using a proxy. Does everyone just make their ETL devs sysadmins and call it a day? I'm leaning toward creating a service account to associate the credential with whose password doesn't expire then creating an SSIS proxy for the credential and granting all of the SSIS devs access to the proxy. At least then they're able to edit the jobs they own, the jobs will succeed so long as the SSIS step uses the proxy, and I'm not having to update credential passwords all the time.


texts: Best open source DB technology to store and query large, sparse matrices

texts:
I have a large vector space model, in which each point is a multi-variate observation. In other words, I have a large number of pretty large records, mostly of ints and floats. I started with in-memory matrices, but given a large number of points (1B+) and a large number of dimensions (1K+), I soon realized that that wasn't an option.

I need to

1) make the whole thing persistent and dynamic (I want to add/update/remove points/dimensions easily)

2) query the VSM efficiently, performing similarity queries (given a point, find top K similar points) and range queries (find all points within a subspace).

Based on some reading, it seems that there isn't an off-the-shelf package to do this. What would you use to implement this kind of scenario? Dumping each point as a row in a relational db didn't work because the large number of dimensions. Even after normalization, the relational db was clearly too slow to perform similarity queries.

Moreover, I need to compute the dot product on chunks of the matrix.
What technology would you recommend in this case? For example, I have read about array databases, and OLAP cubes, but I would like to have some advice from real DBAs.

Thanks, Mulone


texts:
Table info:

Database name: user_motiva
Table name: wp_options.frm  wp_options.MYD  wp_options.MYI  wp_options.TMD

when I do a mysqlcheck -r --all-databases it gets hung on that table even if you let it sit all day.

Is there anther way to fix/repair/recover that table?

Should I use myisamchk? I saw something like:

shell> myisamchk --recover City

My config on a 16GB ram box

 cat /etc/my.cnf
[mysqld]
default-storage-engine=MyISAM
local-infile=0
symbolic-links=0
skip-networking
max_connections = 500
max_user_connections = 20
key_buffer = 512M
myisam_sort_buffer_size = 64M
join_buffer_size = 64M
read_buffer_size = 12M
sort_buffer_size = 12M
read_rnd_buffer_size = 12M
table_cache = 2048
thread_cache_size = 16K
wait_timeout = 30
connect_timeout = 15
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 64M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 64M
query_cache_type = 1
low_priority_updates=1
concurrent_insert=ALWAYS
log-error=/var/log/mysql/error.log
tmpdir=/home/mysqltmp
myisam_repair_threads=4
[mysqld_safe]
open_files_limit = 8192
log-error=/var/log/mysql/error.log

[mysqldump]
quick
max_allowed_packet = 512M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

and could this have happened because of a crashed table from doing killall -9 mysqld because it would not shutdown and restart?

EDIT:

root@server [/var/lib/mysql/user_motiva]# myisamchk -e *.MYI
Checking MyISAM file: wp_options.MYI
Data records:    1827   Deleted blocks:       3
myisamchk: warning: 3 clients are using or haven't closed the table properly
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check data record references index: 2
- check records and index references
MyISAM-table 'wp_options.MYI' is usable but should be fixed
root@server [/var/lib/mysql/user_motiva]# myisamchk --safe-recover wp_options.MYI
- recovering (with keycache) MyISAM-table 'wp_options.MYI'
Data records: 1827
myisamchk: error: Can't create new tempfile: 'wp_options.TMD'
MyISAM-table 'wp_options.MYI' is not fixed because of errors
Try fixing it by using the --safe-recover (-o), the --force (-f) option or by not using the --quick (-q) flag
root@ns2 [/var/lib/mysql/user_motiva]# myisamchk -o -f wp_options.MYI
- recovering (with keycache) MyISAM-table 'wp_options.MYI'
Data records: 1827

Does this mean that it is now fixed? If so how do I move it back? (this was done on a different server)
Is there a way to maybe bring MySQL down on the main server and run a command to fix all the files?


texts: Neo4j: ERR_CONNECTION_REFUSED after restarting a neo4j community created in Azure VM

texts:
I have installed Neo4j community edition on a VM in Azure.
I did this:
0
I tried to install Neo4j community edition on a VM in Azure, I can't access it in the browser.
I did this:

Created a virtual machine in Azure on which you can host a Neo4j community version server I choose Linux (Ubuntu 18.04) virtual machine in Azure

Connect to the virtual machine with Putty and start installing

Installed Java 11 with Putty

Installed the latest Neo4j community version with Putty

Setted up Neo4j to be accessible over the internet: sudo vim /etc/neo4j/neo4j.conf

Add/edit the following lines to the config file:

dbms.connector.bolt.enabled=true dbms.connector.bolt.listen_address=0.0.0.0:7687 dbms.connector.http.enabled=true dbms.connector.http.listen_address=0.0.0.0:7474

To access port 7474 from outside the Linux machine, I added it to the inbound port rules for the virtual machine (but I'm not sure if I did it wright)

After having done all the setup required I went to google chrome and I enter:
http://{ip_of_the_VM}:7474/browser
Everything was working well.
I restarted the VM and with the same adress above I have: ERR_CONNECTION_REFUSED
What do I have to do ?


texts: Unable to connect to pluggable database in Oracle 12c

texts:
I have oracle database Oracle Database 12c Enterprise Edition 12.2.0.1.0, and I created a pluggable database named DEVDB to use it in web application development, I created it using the Database Configuration Assistant, during the creation process I created a user/password to connect to that DB, then I modified the file tnsnames.ora adding this:

 DEVDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = devdb)
    )
  )

Using DBeaver or SQL developer I can connect to the PDB successfully as you can see in the image blow:

but when I try to connect to the DB trying this from sqlplus command line:

sql> conn user/password@DEVDB

I get the following error message:

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Warning: You are no longer connected to ORACLE.

This is show pdbs command result:

   CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB                        READ WRITE NO
         4 DEVDB                          READ WRITE NO

I reloaded lsnrctl many times and tried again but I get the same error.

I would like to be able to connect the PDB from sqlplus command line and understand the cause of that error

Any help please ??


texts:
I need to test SQL Agent over a period of time, so need to change the system clock into the future quite often (and back)... don't worry it's only my local machine not a server :-)

The trouble is when I set it back again to the present date time, the next run date still ad-hears to the future times.

e.g. If I've put my clock into the future and the job is set to run every minute and it was last run in 11/01/2080 01:01:00 then it's next run is 11/01/2080 01:02:00, even if I set the clock back to the present.

It is understandable that this is the normal behaviour of SQL Server. Is there a way of resetting the next run date easily?

I've tried running the job manually but it has no effect on the next run time.


texts: How to restore the privileges of the user postgres in PostgreSQL?

texts:
Someone logged in my PostgreSQL as the user postgres, because a lack of security in my configuration files. What he did is to create a lot of superusers, and then, he revoked SUPERUSER privilege from postgres.

I've corrected the configuration files to avoid more problems, but now the harm is done and I can only log in as postgres, who is no more a superuser.

I would like to grant SUPERUSER privileges again to postgres user, but of course as I can't log in as a superuser I can't do it. Which is the best way to achieve this? I prefer to avoid reinstalling postgresql, is there other way?

I've also glanced the PostgreSQL log and I've seen the moment when the users were created, and their passwords, but these ones are encrypted through MD5 and I'm not able to desencrypt them.

What would you do in this case? Thank you!


texts: How to grant Connect & Create Privilege on current Database in PostgreSQL?

texts:
After looking at the docs, I know that you can grant Connect & Create Privileges by using the following commands:

GRANT CREATE ON DATABASE <db_name> TO user;
GRANT CONNECT ON DATABASE <db_name> TO user;

How do I grant these privileges on the current database without specifying it's name?

I'm trying to write a script which will be used to grant all rights whenever a new database is created by my application, and hence I don't know what the name will be while writing the script.


texts:
Is it possible to turn off serialization failure logging?  If so, how?

For example, my log is filled with this example:

ERROR:  could not serialize access due to read/write dependencies among transactions
GMT DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
GMT HINT:  The transaction might succeed if retried.
GMT STATEMENT:  COMMIT

log_statement is commented so left to the default none.


texts:
I have a sql server  table with meter values which are read every minute each day which should give me 1440 records per meter per day. Sometimes the data doesn't come in every minute and as such a row can be missed. How would I insert a row with a 0 or null value to complete the intervals.

Example

Datetimecolum        kWh
2017-01-01 12.00 AM  64.00
2017-01-01 12.01 AM  0.02
2017-01-01 12.03 AM  2.32

I would like to populate a row for 12.02 AM with 0 or null value to indicate the reading was not received. Any help would be appreciated.

Thanks


texts: In Postgres, how do I cast character varying to an enum type?

texts:
I have a Postgres 8.4 table with columns that are of type "character varying", but I want to convert them to an enum type.

When trying to alter the table

ALTER TABLE logs ALTER COLUMN interface_type TYPE interface_types
USING interface_type::text::interface_types;

I get an error

default for column "interface_type" cannot be cast to type interface_types

The default value for the column IS a text version of a value in the enum types. So it should be possible, though I've had no luck googling for the solution.

I assume that I need to define a cast for it. How?


texts: MySQL InnoDB logfiles resized - review mysql-err.log for potential problems

texts:
I restarted mysqld this morning, to increase the size of the innodb logs and increase the amount of memory allocated to the innodb buffer pool.  (MySQL 5.1.53-Community on Ubuntu)

To increase the size of the innodb logs, I modified the my.cnf file, stopped mysqld, renamed the existing innodb log files, and then started mysqld again.

     root@mysql01:# /etc/init.d/mysql.server stop
     Shutting down MySQL
     ............ *
     root@mysql01:# ls -al ib_logfile*
     -rw-rw----  1 mysql mysql  251658240 2013-02-08 08:21 ib_logfile0
     -rw-rw----  1 mysql mysql  251658240 2013-02-08 08:21 ib_logfile1
     root@mysql01:# mv ib_logfile0 ib_logfile0.OLD
     root@mysql01:# mv ib_logfile1 ib_logfile1.OLD
     root@mysql01:# /etc/init.d/mysql.server start
     Starting MySQL
     ................................ *
     root@mysql01:#

The mysql01-err.log shows

130208  8:21:27 [Note] /opt/mysql/bin/mysqld: Normal shutdown

130208  8:21:27 [Note] Event Scheduler: Purging the queue. 0 events
130208  8:21:30  InnoDB: Starting shutdown...
130208  8:21:39  InnoDB: Shutdown completed; log sequence number 661 1658279774
130208  8:21:39 [Note] /opt/mysql/bin/mysqld: Shutdown complete

130208 08:21:39 mysqld_safe mysqld from pid file /opt/mysql_data/mysql01.pid ended
130208 08:22:17 mysqld_safe Starting mysqld daemon with databases from /opt/mysql_data
130208  8:22:17 [Note] Plugin 'FEDERATED' is disabled.
130208  8:22:18  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 1536 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500
130208  8:22:29  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 1536 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
130208  8:22:37  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...
InnoDB: Last MySQL binlog file position 0 886154568, file name ./mysql-bin.001636
130208  8:22:48  InnoDB: Started; log sequence number 661 1658279948
130208  8:22:48 [Note] Event Scheduler: Loaded 0 events
130208  8:22:48 [Note] /opt/mysql/bin/mysqld: ready for connections.
Version: '5.1.53-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)

I'm concerned with the line, after the new innodb log files are written, that says

130208  8:22:37  InnoDB: Database was not shut down normally!

My questions are:

Is that line expected in the log file, is this the behavior I should expect? Does this indicate a problem, or is this a normal result from creating new log files?

Is there anything in the tail of the mysql01-err.log that indicates that there was a real problem?

Was the InnoDB "crash recovery" successful? Or is there a chance that something is screwed up?

Are there any additional steps that I can take to verify that everything is fine with InnoDB, and that the steps I performed did not cause any corruption?

(I'd rather know about any problems now. It would be much better to do any additional steps to get a full recovery, sooner, rather than later.)


texts: Where is the MySQL variable - innodb_flush_method?

texts:
I would like to tweak the value of innodb_flush_method to find out its performance impact on a database server. That variable is listed when I run the command SHOW VARIABLES.

But I could not find it in the configuration file for the MySQL Server - my.cnf.
I'm using XAMPP 1.6.3 with PHP (5.2.3) and MySQL (5.0.45). Its MySQL configuration file path is /xampp/mysql/bin/my.cnf
I searched the variable in the file, but no luck.
Where can I find it? Am I just overlooking?

UPDATE
I upgraded to XAMPP 1.8.1. Its configuration file path is /xampp/mysql/bin/my.ini
Still could not find that variable.


texts: I'm having an issue all of a sudden with SSMS 18.  Today, the authentication method is grayed out and I cannot get it to connect to a server.  I've rebooted, reinstalled 18.6,  uninstalled and installed 18.5 and run it as an Administrator.  I have no idea what the issue could be and cannot seem to locate any info on through Google searches.


texts: Is there a way to log each and every executing query and its corresponding result in MySQL?

texts:
I have a requirement, where I want to log each and every executing query and it's corresponding results set to a log file in mysql. Is there any way I can achieve this?

Ex:-

Query: select * country_name from countries_tbl

Result: USA, China, Japan


texts: How to Know if I have A Restricted or Full Access to Table

texts:
This just came to me when helping my friend with his homework.
Is it possible to know our role (implicit, not know to us) role grants as full access to a full table or just to a view derived from the table?
 The table [Table]  contained a field[Class] indexed alphabetically, i.e., A,B,C,...,Z . The view
contained just a subset , something like [class] , with values  A,B, E,G,J. Now, if I was given a role with access to the latter table with that indexing , missing letters between B,E , E and G , etc., I would suspect I have access to a view and not the whole table. Still, is there a way, maybe by joining with/to other tables of knowing that my access is restricted?   Maybe joining to a table containing the same PK as [Table] , without  the  Class field, maybe getting some Nulls as outputs? Basically, are there ways of knowing if I am viewing a full table or just a view derived from it? Maybe by trying to do something "full tables" can do but views cannot?

EDIT: Thanks for the answers, I was thinking more of indirect ways of getting an answer, like querying other tables on joins on my present table's PK  ( assuming, of course, I have SELECT access to these tables ) and getting nulls, or by doing a " Where PK is null" query and getting some answers.


texts: Can High CPU be triggered by Login Failures?

texts:
On our SQL Server (used for TFS) we have recently had the CPU pegged at 100% (with a few occasional dips) for nearly a day and a half.

Looking at the SQL Logs, there appears to be a correlation in time between seeing "Login Failure" errors for a 3rd Party App and the prolonged CPU chewing. The Login Failure errors appeared at about the time yesterday when the problems were reported.

Even a restart of the SQL Server didn't alleviate the problem, it just went back to 100% soon after coming back up.

Myself and one of our DBA's have had a good hard look at the SPID's in SQL and there is nothing obvious or different to any other time.

This box is a little under-provisioned and spends most of its life at about 70-80% CPU.

From the SQL Logs the following messages repeat at about 20-60 second intervals:

Date        05/11/2014 11:33:48
Log     SQL Server (Archive #3 - 05/11/2014 12:04:00)

Source      Logon

Message
Login failed. The login is from an untrusted domain and cannot be used with Windows
authentication. [CLIENT: w.x.y.x]
_________________________________________________________________________________________

Date        05/11/2014 11:33:48
Log     SQL Server (Archive #3 - 05/11/2014 12:04:00)

Source      Logon

Message
Error: 18452, Severity: 14, State: 1.
_________________________________________________________________________________________

Date        05/11/2014 11:33:48
Log     SQL Server (Archive #3 - 05/11/2014 12:04:00)

Source      Logon

Message
SSPI handshake failed with error code 0x8009030c, state 14 while establishing a
connection with integrated security; the connection has been closed.
Reason: AcceptSecurityContext failed.
The Windows error code indicates the cause of failure.  [CLIENT: w.x.y.x].
_________________________________________________________________________________________

Date        05/11/2014 11:33:48
Log     SQL Server (Archive #3 - 05/11/2014 12:04:00)

Source      Logon

Message
Error: 17806, Severity: 20, State: 14.

There are also similar Audit Failure errors in the Windows Security Event Log that match the Client IP address and times.

Could something like this cause the last of the CPU to be consumed?

Since logging off all users on the client w.x.y.z I have not seen any more login failures and CPU has returned to normal levels.


texts: How to put the results of a query into a variable?

texts:
I have written a Postgres anonymous block. In which I have written join query on multiple tables like:

select a.portal.*, a.p_fb_config.*, a.p_gplus_config
from a.portal
   left outer join a.p_fb_config on a.portal.pid = a.p_fb_config.pid
   left outer join a.p_gplus_config on a.portal.pid = a.p_gplus_config.pid;

Now I want to catch this value in variable. So how should I declare that variable?

I need something like:

         portal_row a.portal%ROWTYPE;//for 3 tables


texts: How can I allow a user to query a view but not the underlying table in MySQL?

texts:
I have searched for this question but cannot find an answer.  Or, the answers I have found have been related to SQL Server.  This is for MySQL 5.x.

Anyway, say I have a table called Employees and in that table, I have various columns like:

id, lastname, firstname, salary, ssn, hiredate

Now, I have some users that need to be able to select from the VIEW but not the TABLE.

For example:

--user a
select * from employee_view;

--shows:
id, lastname, firstname, hiredate

--user a
select * from employee;   // access denied  (didn't use view)

Then I have an HR User:

--HR user
select * from employee_view;

-- shows:
id, lastname, firstname, ssn, salary, hiredate

-- HR user
select * from employee;  // access denied (didn't use view)

Is there any way I can do this?

Thanks.


texts: Update nth element of array using a WHERE clause

texts:
I have a product table with a jsonb column named 'metadata' in a PostgreSQL 10 database. It's my first time working with documents and Postgres. jsonb values look something like this:

{
  "name" : "l33t shirt",
  "price" : "1200",
  "quantity": "60",
  "options" :
    {
      "type" : "radio",
      "title" : "color",
      "opts" : [
        {"value" : "red" , "price" : "-100" , "qty" : "30"  },
        {"value" : "blue" , "price" : "+200" , "qty" : "10"  },
        {"value" : "green" , "price" : "+300" , "qty" : "20"  }
      ]
    }
}

Two questions:

1. How can I select a specific element in the "opts" array?

select metadata->'options'->'opts'->(element here) from product
where  metadata->'options'->'opts' @> '[{"value" : "blue"}]'

2. How to update the "qty" (subtract from its current "qty") when one or more of them is sold?

Further links to guides / notes are appreciated.


texts: Which backup files are needed for a successful restore?

texts:
Considering the built-in 'Backup Database' script (backup.bat) that comes with a new installation of the Oracle Express 11g Edition, after finishing the script execution, what folders/files should be copied to an external media (XCOPY backup) so the database could be fully restored the database after a new install?

Environment:

Windows Server 2003
Database is in ARQCHIVELOG
Datafiles/Tablespaces are set-up as default (user data is being allocated in USERS Tbs)

I have a guess that the folder [InstallDir]\oraclexe\app\oracle\fast_recovery_area should contain all of what I need, but I'm afraid it isn't enough.

PS: Question moved from ServerFault: https://serverfault.com/questions/413762/oracle-database-11g-express-edition-built-in-backup-files-to-be-saved (too old to migrate)


texts: PostgreSQL: Calculate balance working backwards from current balance

texts:
I have a balances table that stores the current_balance for bank accounts; and a transfers table that contains a row for every transfer to- and from an account (deposits are positive numbers, withdrawals are negative).

I need to create an account summary that contains each transfer for an account, as well as the balance resulting from that transfer.

Example:

+--------------------------+--------+---------+
|      activity_date       | amount | balance |
+--------------------------+--------+---------+
| 2015-12-24T20:27:00.670Z |     10 |     180 |
| 2015-12-19T12:13:50.085Z |   -275 |     170 |
| 2015-12-18T23:56:22.513Z |     10 |     445 |
| 2015-12-18T23:54:46.880Z |     50 |     435 |
| 2015-12-17T03:32:10.707Z |   -120 |     385 |
| 2015-12-12T03:56:50.775Z |     35 |     505 |
| 2015-12-11T23:09:40.211Z |    -20 |     470 |
| 2015-12-03T01:17:59.460Z |    -10 |     490 |
| 2015-11-23T15:39:35.003Z |    500 |     500 |
+--------------------------+--------+---------+

Since I only have the current balance for the account, I need to start there and work my way backward to a given date. In a spreadsheet, I would calculate the new balance by subtracting the amount of the transfer from the previous balance, but I'm having trouble translating this into SQL.

Schema

CREATE SCHEMA temp;

CREATE TABLE temp.balances (
  account_id      INT
, current_balance INT
);

CREATE TABLE temp.transfers (
  account_id      INT
, activity_date   TIMESTAMP
, amount          INT
);

INSERT INTO temp.balances (account_id, current_balance)
VALUES (1, 180);

INSERT INTO temp.transfers (account_id, activity_date, amount)
VALUES (1, '2015-12-24T20:27:00.670Z', 10)
, (1, '2015-12-19T12:13:50.085Z', -275)
, (1, '2015-12-18T23:56:22.513Z', 10)
, (1, '2015-12-18T23:54:46.880Z', 50)
, (1, '2015-12-17T03:32:10.707Z', -120)
, (1, '2015-12-12T03:56:50.775Z', 35)
, (1, '2015-12-11T23:09:40.211Z', -20)
, (1, '2015-12-03T01:17:59.460Z', -10)
, (1, '2015-11-23T15:39:35.003Z', 500);

SQL

WITH transfers AS (
  SELECT *
  FROM temp.transfers
  WHERE account_id = 1
    AND activity_date BETWEEN '2015-10-01'::DATE AND CURRENT_TIMESTAMP
  ORDER BY activity_date DESC
)
SELECT
  t.activity_date
, t.amount
, (LAG(balance, 1, b.current_balance) OVER (ORDER BY t.activity_date DESC)::INT) - (LAG(t.amount, 1, 0) OVER (ORDER BY t.activity_date DESC)::INT) AS balance
FROM transfers t
JOIN temp.balances b ON b.account_id = t.account_id

This query fails because I am trying to access the value of balance that is being generated by the window function. I looked into WITH RECURSIVE, which seems like the right technique to use, but couldn't get it to work. I'm also not sure if it can be used in conjunction with other WITH clauses, which I would need to do.

So my question is: How can I calculate the balance after each transfer, working backward from the current balance, in PostgreSQL 9.3?

Update

Using a slightly-modified version of Julien's answer below, I tried this:

WITH transfers AS (
  SELECT *
  FROM temp.transfers
  WHERE account_id = 1
    AND activity_date BETWEEN '2015-10-01'::DATE AND CURRENT_TIMESTAMP
)
SELECT
  t.activity_date
, t.amount
, b.current_balance - SUM(t.amount) OVER(ORDER BY t.activity_date DESC) AS balance
FROM transfers t
JOIN temp.balances b ON b.account_id = t.account_id

...which is almost correct, except that the balances are shifted up one row.


texts: Create MySQL CHILD and PARENT records at the same time and set CHILD.PARENT_ID by using MySQL Triggers

texts:
I want to create relationships between parent and child when booth doesn't exist and when new child is created. This somehow can be done with Triggers but i don't know how to work with SQL.

After some googling i came up with Trigger concept bellow.

Create child
Create parent
Get last parent id
Update CURRENT child record with parent_id

Could somebody help me to achieve this?

DELIMITER $$
CREATE
    TRIGGER `database_name`.`trigger_name` AFTER INSERT
    ON `database_name`.`child_table`
    FOR EACH ROW BEGIN

    /*Step 1 - Create Parent record.*/
    INSERT INTO  `database_name`.`parent_table`(id) VALUES (``);

    /*Step 2 - Get last inserted ID of just created Parent record
      and assign to the variable*/
    DECLARE last_id int;

    SELECT LAST_INSERT_ID INTO last_id;
    FROM information_schema.tables
    WHERE table_name = `parent_table`
    AND table_schema = `database_name`;

    /*Step 3 - Update current record child_table.parent_id with last_id variable*/
    UPDATE NEW.parent_id = last_id;
END$$
DELIMITER ;

Or something like this, but it has errors.

DELIMITER $$

DROP TRIGGER IF EXISTS create_new_child; $$

CREATE
    TRIGGER create_new_child BEFORE INSERT
    ON child
    FOR EACH ROW BEGIN
    DECLARE last_id INT;
        SELECT AUTO_INCREMENT INTO last_id
        FROM information_schema.TABLES
        WHERE TABLE_NAME = parent
        AND TABLE_SCHEMA = DATABASE();
    INSERT INTO parent (title) VALUES (`TEST`);
    SET NEW.parent_id = last_id;
    END$$
DELIMITER ;

Unknown column 'parent' in 'where clause'


texts: Can I use SQL Server Standard Edition as Primary and Enterprise SQL Server as Secondary node?

texts:
I have Software Assurance for SQL Server Enterprise Edition and I need to setup High Availability on it.
The issue is my primary SQL Server has a Standard license.
Could I add SQL Server Enterprise edition as secondary replica?


texts: Migrate from SQL Server 2016 Failover Cluster to Availability Groups

texts:
We have a 16 node SQL Server 2016 failover cluster with around 20 SQL Server instances and total number of databases around 600.

Since we are upgrading our hardware to Hyper-Converged infrastructure which has limitations with failover clusters with shared disks then we have to migrate our cluster to availability groups.

My question is: what should we consider if we are going to migrate to SQL Server availability groups? can we have the same number of instances and multiple availability groups on the same servers as we have in the current failover cluster?

Appreciate if someone guide us with some technet articles which talks about similar cases.


texts: Finding the end of a Relationship chain optimally

texts:
I have a table that contains an id column, a parent column, and it's child column. So for every record, I know what record comes before it and what will come after it in a chain relationship, but from the individual record, I don't know what position it is within the chain nor what the first part or last part of the chain.

For demonstration purposes, assume we have the following setup. I realize that there are many design issues with this type of setup, but this is what I have to work with.

CREATE TABLE Relationships (
ID VARCHAR(4),
ParentID VARCHAR(4),
ChildID VARCHAR(4)
)

-- Insert root entries with their children
INSERT INTO Relationships (ID, ParentID, ChildID)
VALUES ('0001', '', '0003'), ('0002', '', '0004')

-- Now add further entries for each relationship chain
INSERT INTO Relationships(ID, ParentID, ChildID)
VALUES('0003', '0001', '0005'), ('0005', '0003', '0006'), ('0006', '0005', '0007'), ('0007', '0006', ''),
  ('0004', '0002', '')

--Now we have two chains of 0001 -> 0003 -> 0005 -> 0006 -> 0007 and 0002 ->   0004

With a recursive CTE like below, I can find how all records are related in their chain and their position within the chain.

WITH RelationshipChain AS (
SELECT ID, ParentID, ChildID, 0 AS Seq, ID AS RootID
FROM Relationships WHERE ParentID = ''
UNION ALL
SELECT r2.ID, r2.ParentID, r2.ChildID, rc.Seq + 1 AS Seq, rc.RootID AS RootID
FROM Relationships r2
INNER JOIN RelationshipChain rc ON rc.ChildID = r2.ID
)

SELECT * FROM RelationshipChain
ORDER BY RootID, Seq

For about 2 million records this runs in about 30 seconds which is pretty good, however if I try to also include what the final part of the chain, it takes 4x as long to run. Currently, I am doing it like this:

WITH RelationshipChain AS (
SELECT ID, ParentID, ChildID, 0 AS Seq, ID AS RootID
FROM Relationships WHERE ParentID = ''
UNION ALL
SELECT r2.ID, r2.ParentID, r2.ChildID, rc.Seq + 1 AS Seq, rc.RootID AS RootID
FROM Relationships r2
INNER JOIN RelationshipChain rc ON rc.ChildID = r2.ID
)

SELECT *
FROM RelationshipChain rc
CROSS APPLY (
SELECT MAX(Seq) AS FinalSeq FROM RelationshipChain WHERE rc.RootID = RootID
) AS b
CROSS APPLY (
SELECT ID AS LastChild FROM RelationshipChain WHERE b.FinalSeq = seq AND rc.RootID = RootID
) AS c
ORDER BY RootID, Seq

Is there a way to do this more efficiently?


texts: If all tables are myisam, should I use myisam engine instead of default innodb?

texts:
I have read all of the innodb/myisam threads and they are confusing.  Everyone says innodb but I am assuming that they are saying innodb engine with innodb tables.?

The software I am using creates myisam tables so I was wondering if I should switch from the default innodb engine to myisam engine and disable innodb since I do not have a single table that is innodb?


texts: is it recommend to set "--setParameter ttlMonitorEnabled=false" for better performance if you don't use TTL Indexes

texts:
We run a few sharded clusters and many replicasets. Our customers don't use TTL Indexes.

Will there be a better performance if we disable background thread that is responsible for deleting documents from collections with TTL indexes?

mongod --setParameter ttlMonitorEnabled=false

I found an old (2013) Time to live (TTL) slows down mongod post on mailing list. I don't know if this is a edge case or general optimizion.

Quote from SERVER-10033

  May be used to stop TTL deletes if the system is under extreme load or
  during an outage/corruption investigation. A single global (or per-DB)
  setting would be easier than having to update or drop all TTL indexes.


texts: How to attach a sql db with blobs using a shared folder?

texts:
I use the following query for attaching a sql db:

CREATE DATABASE [myDb] ON
 (FILENAME=N'C:\myDb\myDb.mdf'),
 (FILENAME=N'C:\myDb\myDb_log.ldf'),
 (FILENAME=N'C:\myDb\FILESTORE')
FOR ATTACH

In general it works well. I have a bit complicated case. I have to use a shared folder for attaching my db. I create a shared folder using the following command:

net share myShare=C:\myDb

Attaching a db from shared folder works well until the db doesn't have blobs.
If db has blobs sql returns the following error:

The path '\\localhost\myShare\FILESTORE' cannot be used for FILESTREAM files. For information about supported paths, see SQL Server Books Online.
Msg 5135, Level 16, State 2, Line 2
The path '\\localhost\myShare\FILESTORE' cannot be used for FILESTREAM files. For information about supported paths, see SQL Server Books Online.
Msg 5120, Level 16, State 101, Line 2
Unable to open the physical file "\\localhost\myShare\FILESTORE". Operating system error 5: "5(Access is denied.)".
Msg 1802, Level 16, State 7, Line 2
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

The sort of workaround for this problem is creating a symbolic link for blob folder.

mklink /D "C:\slink_myDb" "\\localhost\myShare"

and then

CREATE DATABASE [myDb] ON
 (FILENAME=N'\\localhost\myShare\myDb.mdf.mdf'),
 (FILENAME=N'\\localhost\myShare\myDb_log.ldf'),
 (FILENAME=N'C:\slink_myDb\FILESTORE')
FOR ATTACH

It works with almost all sql servers but it looks like since sql server 2016 it was disabled. sql server 2016 returns this:

Msg 5120, Level 16, State 106, Line 9
Unable to open the physical file "C:\slink_myDb\FILESTORE". Operating system error -1073741766: "0xc000003a(failed to retrieve text for this error. Reason: 317)".
Msg 5105, Level 16, State 14, Line 9
A file activation error occurred. The physical file name 'C:\slink_myDb\FILESTORE' may be incorrect. Diagnose and correct additional errors, and retry the operation.
Msg 1813, Level 16, State 2, Line 9
Could not open new database 'myDb'. CREATE DATABASE is aborted.

How to attach a sql db with blobs via unc paths ?
(without copying; db files are local, sql server is remote)

ps
All needed permission ntfs and sharing are granted.
I've tried fsutil

fsutil behavior set SymlinkEvaluation L2L:1 R2R:1 L2R:1 R2L:1


texts: Oracle RMAN Back-up files in red hat can be read in windows?

texts: We have performed a cold back-up of a database using Oracle RMAN. The back-up ran on a Red Hat Server and then the files were copied to a mounted external hard-drive. Can this back-up files then be read in a windows PC or there will be some file system compatibility issue? When restoring the back-up with RMAN, does the hdd needs to be mounted in a linux server or it can be any server?


texts:
I have a value like "this - is - a -comment". However I need to run an update statement to add a space after the last - (-comment above should be - comment)

Any able to help me with an update statement?


texts:
Need urgent help on this. We have applied latest sql patches on one of the server (SQL 2014, SP3, CU4) and somehow we were not able to start the SQL due to following error.
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
Script level upgrade for database 'master' failed because upgrade step 'msdb110_upgrade.sql' encountered error 200, state 7, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
Error: 3417, Severity: 21, State: 3.
Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it.
So, i have started the server with Trace flag 902 and run the script msdb110_upgrade script and it failed with
Msg 468, Level 16, State 9, Procedure #syscollector_upload_package, Line 37659
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the not equal to operation.
Can anyone please help to fix this issue?


texts: ERROR: please confirm the keyfile is match and keyring plugin is loaded

texts:
I want to create a copy of my Production DB to a new instance.

There is a innobackupex running on the prod every night looks like this:

innobackupex --user=username --password=password --keyring-file-data=/var/lib/mysql-keyring/keyring --target-dir=mybackup-path

I copied the mybackup-path contents to my new instance and then prepared it by as following:

xtrabackup --prepare --keyring-file-data=/var/lib/mysql-keyring/keyring --target-dir=mybackup-path-in-new-server

then copy over files --copy-back and chowned the data folder to mysql & everything is working

BUT I get "please confirm the keyfile is match and keyring plugin is loaded".

I have added updated my.cnf to early-plugin-load but still nothing

early-plugin-load = keyring_file.so
keyring_file_data = /var/lib/mysql-keyring/keyring

When I start mysql, and run SHOW PLUGINS, Keyring is ACTIVE,

My both instances are the same and both have ubuntu and Percona Server 5.7

I'm sure I'm missing something, and I believe it is related to keyring,
can someone help?


texts: How to resolve RESOURCE_SEMAPHORE and RESOURCE_SEMAPHORE_QUERY_COMPILE wait types

texts:
We are trying to figure out root cause of slow running sql server queries hitting/fetching data from one of the database , size 300 GB, hosted on server with below configuration:

  Windows server 2003 R2, SP2, Enterprise Edition, 16 GB RAM , 12  CPU'S
  32 Bit

  SQL server 2005, SP4, Enterprise Edition, 32 Bit.

We have already informed business on the upgrade to 64 bit which would take over a month.

But for the current issue, we are trying to gather the data if we can resolve the memory pressure or finally come to a conclusion to increase RAM.

Action Completed: Re-indexing and update stats are proper for this DB.

As shown below, we have been noticing the semaphore waittype for past 5 days, ran during the load hours:

Few info after below  queries: size of buffer= 137272

SELECT SUM(virtual_memory_committed_kb)
FROM sys.dm_os_memory_clerks
WHERE type='MEMORYCLERK_SQLBUFFERPOOL'

and semaphore memory= 644024 per below query

 SELECT SUM(total_memory_kb)
FROM sys.dm_exec_query_resource_semaphores

Below is some more info gathered from dm_exec_query_resource_semaphores and sys.dm_exec_query_memory_grants dmv's

So from above info gathered and per SP_Blitz data Resource semaphore seems to be the problem.

Is memory 'target_memory_kb' assigned for resource semaphore id's too low, as compared to 16 GB RAM available.

Note* per analysis on 8 hours run 'target_memory_kb' is always under 1 GB, compared to 16 GB available?

what could be the issue here and how to resolve, please suggest

Thanks


texts:
I’m looking for a data store to back an API, and since I know of no data store that handles it’s data they way I’d like, I’m thinking good ol’ PostgreSQL might be suitable for now.
Only problem is that I have no idea how to implement it (RDMS never really was my strong side).
The are the constraints/limits I’m working with:

Data is written to the data store in identically sized chunks (say 4kb)
Data must be persisted to disk
Each chunk of data must be read at most once by consumers (read, then delete)
Data need not be read in the same order it was written
I’ve got complete control of the process writing the data and the process reading the data
Scaleability - Doesn’t need to scale to google-level, but I’d like for it to be somewhat scaleable.

My biggest concern is how to structure a a table to allow efficient read-once-then-delete. I was thinking of using row locks, but haven’t had much luck looking at the documentation to figure out if it’s feasible.
I was also planning on handling the reading of data in the database instead of in the application using stored procedures. I think it would work, but I can’t wrap my head around the whole locks thing.
So, is this feasible to do using Postgres, and if so, go any pointers as to how you’d achieve it?


texts:
We have an Oracle Database data warehouse that averages 120 commits per second (according to AWR).  The top wait activity is log file sync, which is to be expected... For now, overall response times are satisfactory, however, we are concerned with scalability, going forward.

Unfortunately, due to the nature of the vast majority of these transactions, the usual fix of "change your application to commit less often" isn't an option.

We are giving serious thought to changing these commits over to asynchronous batch commits via: COMMIT WRITE BATCH NOWAIT;

We are certainly aware that this would be breaking the "D" in ACID, namely that committed transactions could be lost in the event of an instance crash.  However, most of the aforementioned commits is logging information where if we lose a transaction here and there from an instance crash, the consequences of those lost transactions is very minimal.

I understand that with batch nowait commits, the commits are buffered in the log buffer (which we can tune via the log_buffer init parameter, if necessary).  However, i'm unable to find answers to the following questions:

1) Do the batch commits span multiple connections?  In other words, if we:

 open a connection
 do work
 commit write batch nowait;
 close the connection
 open another connection
 do work
 commit write batch nowait;
 close the connection
 rinse, repeat...

Are both of those commits batched or does Oracle flush the commits to disk for each connection when the connection is closed?

2) Is there anyway to find out how often the batched commits are written to disk and become "durable"?

3) Are the batched commits written to disk serialized?  In other words: if the application batch commits "A" work, then batch commits "B" work, is "A" work always written to disk before "B" work or is it possible for "B" work to be written to disk before "A" work is written to disk, instance crashes, "A" work is lost but "B" work is durable?


texts: How to avoid large joins when determining which rows a user is authorized for?

texts:
Typical wep app situation: you have logged in users querying for resources exposed through an API. As a hypothetical example, company administrators quering for their employees' time sheets:

company <- employees <- time_sheets
company <- admin

Say I wanted to get all the time sheets that I'm authorized for/I have access to as an admin of that company.

Without any optimizations, in the above I'd have to join admin with company with employees with time_sheets to determine all the time_sheets I can look at. You can see how for a wider schema the joins could add up very fast. I realize that in modern databases joins are ludicrously fast, but I'm guessing this is still a case you don't want to completely ignore.

Is there a good solution to this? One could potentially have a separate admin to time_sheets many to many join table just to speed-up those queries, but then maintaining it might be tricky.

Any suggestions?


texts:
I know this issue can be solved using unique key constraint on column and some insert command variation.

But I exactly want to know that at last why the following approach goes wrong sometimes and a duplicate entry is added:

a value is checked in the column
if record returns is > 0 then update is issued, otherwise simple insert command

Most of the times it works. You can say more than 98%, but why does it fail sometimes?

I just want to understand where the glitch is in this approach because record existence is checked first.


texts: Create a copy of the database in same server

texts:
I am trying to create a copy of database in mysql using mysqldbcopy like :

mysqldbcopy --source=root:xxx@13.67.59.xxx --destination=root:xxx@13.67.59.xxx old_db:new_db

I am getting error saying cannot connect to the destination server (Error code 110).
Source and destination is same server. I want to know which user and ip i need to put here, whether to put mysql username and password or server username and password. Please help me out in this.


texts: How to run insert statement as much times as select statement gives unique values?

texts:
DB Firebird.

I have the following statement

SELECT a.ID FROM CLIENTS a

And I want my INSERT statement into another table to use each of this value, something like

INSERT INTO BANKS (ID, NR, MONEY)
VALUES (
   gen_id(gen_name, 1),
   "there should be client ID from the SELECT statement above",
   3000
)

Can I achieve this with one statement?


texts: MySQL - how to optimize select within a range bound by values in 2 different columns - innodb

texts:
How do I optimize select within a range bound by values in 2 different columns with MySQL innodb? I'm matching words to the pages on which they were printed.
Each instance of a word points to only 1 page. I need to point to the page table from the word instance table. To populate that foreign key, we should compare offsets saved from the file that contained the pages of words.
CREATE TABLE book (
  bid SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY Key
  // other columns not related to question
  );

CREATE TABLE page (
  pid MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY Key
  , bid SMALLINT UNSIGNED NOT NULL
  , CONSTRAINT fk_pid_bid FOREIGN KEY (bid) REFERENCES book(bid) ON DELETE CASCADE ON UPDATE CASCADE
  , page VARCHAR(5) NOT NULL // this could be a roman numeral
  , offset_min MEDIUMINT UNSIGNED NOT NULL
  , offset_max MEDIUMINT UNSIGNED NOT NULL
  );

CREATE TABLE word_instance (
  wiid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY Key
  , bid SMALLINT UNSIGNED NOT NULL
  , CONSTRAINT fk_wiid_xid FOREIGN KEY (bid) REFERENCES book(bid) ON DELETE CASCADE ON UPDATE CASCADE
  , offset MEDIUMINT UNSIGNED NOT NULL
  , pid MEDIUMINT UNSIGNED
  , CONSTRAINT fk_wiid_pid FOREIGN KEY (pid) REFERENCES page(pid) ON DELETE SET NULL ON UPDATE CASCADE
  // other columns not related to question
  );

To populate word_instance.pid one book at a time, I'd like to do something like this, but I can't find an example of how it's done in MySQL:
  UPDATE word_instance
  INNER JOIN page on page.offset_min >= word_instance.offset and page.offset_max <= word_instance.offset
  SET word_instance.page.pid = page.pid
  WHERE page.bid = <some id> and word_instance.bid = <some id>

I am new to MySQL and thinking about design. Am I on the right track? Is there anything special to do with the page table indexes to make this perform better? I have not attempted to create indexes yet because that is part of my question.


texts: How to add a check constraint in Postgres without locking the table?

texts:
I would like to add a check constraint to a very large table. Something like:

ALTER TABLE "accounts" ADD CONSTRAINT "positive_balance" CHECK ("balance" >= 0);

Unfortunately PostgreSQL 9.3 blocks reads or writes until the constraint check has been completed. I verified this by starting a transaction, running the ALTER TABLE, then opening a second transaction and checking that I couldn't read or write from the table until the first transaction completed.

Is there any way I can add this CHECK constraint without locking the table?


texts: Postgres update a table (only) if it exists

texts:
I have a Server and several Client Applications. The Server has to be started before a client can be started. The clients then create tables in the database if they don't exists.

When the Server is started (some tables do not exist) and the following query gives me an exception:

UPDATE recipes SET lock = null
WHERE lock IS NOT NULL;

Relation >>recipes<< does not exists

I want to avoid this exception by checking if this table exists or not.

UPDATE recipes SET lock = null
WHERE lock IS NOT NULL AND
WHERE EXISTS (
   SELECT 1
   FROM   information_schema.tables
   WHERE  table_schema = 'public'
   AND    table_name = 'recipes'
   );

But this query does not work. Can you tell me where my mistake is?


texts: Oracle database, convert XMLTYPE table from basicfile lob to binary xml

texts:
Trying to import an Oracle EBS database (currently in a 19c staging database) into 19c Autonomous Data Warehouse, which won't accept XMLTYPE tables with "STORE AS BASICFILE LOB", but will with "STORE AS SECUREFILE BINARY XML".
So need to change storage of XMLTYPE columns from "STORE AS BASICFILE LOB" to "STORE AS SECUREFILE BINARY XML" with as few changes to syntax as possible. Reason is there are 80 tables to be changed, all with several columns, and all different.
Original DDL to create table;
CREATE TABLE APPS.cpLoadBalance1588_TAB OF XMLTYPE
  XMLSCHEMA "http://xmlns.oracle.com/CRM/Scheduler/Rules" ELEMENT "cpLoadBalance"
 VARRAY "XMLEXTRA"."NAMESPACES" STORE AS BASICFILE LOB "NAMESPACES2143_L"
 VARRAY "XMLEXTRA"."EXTRADATA" STORE AS BASICFILE LOB "EXTRADATA2142_L"
 VARRAY "XMLDATA"."SYS_XDBPD$" STORE AS BASICFILE LOB "SYS_XDBPD$2141_L";

So tried this but get error "ORA-00922: missing or invalid option";
CREATE TABLE APPS.cpLoadBalance1588_TAB OF XMLTYPE
  XMLSCHEMA "http://xmlns.oracle.com/CRM/Scheduler/Rules" ELEMENT "cpLoadBalance"
 VARRAY "XMLEXTRA"."NAMESPACES" STORE AS SECUREFILE BINARY XML "NAMESPACES2143_L"
 VARRAY "XMLEXTRA"."EXTRADATA" STORE AS SECUREFILE BINARY XML "EXTRADATA2142_L"
 VARRAY "XMLDATA"."SYS_XDBPD$" STORE SECUREFILE BINARY XML "SYS_XDBPD$2141_L";


texts: SSMS 2014 (and 2012) CTRL+TAB requires pressing enter

texts:
Sometime last week my CTRL+TAB behavior changed.  I used to hit CTRL+TAB, release, then I would be in my previous document tab.  Now I hit CTRL+TAB, release, and the nav window is still open requiring me to hit enter.

I have tried restarting ssms and rebooting several times.  This is an issue in both SSMS 2012 and 2014.  It is not an issue in Visual Studio (2010 and 2013).

In all VS 2010/2013 and SSMS 2012/2014 the hotkey Ctrl+Tab (Global) is set to Window.NextDocumentWindowNav.  Though I have tried modifying it then putting it back.

CTRL+F6 works the same as it always has.

Is there a way to get the CTRL+TAB behavior back?


texts: Order of operation with LTRIM/RTRIM/ISNULL

texts:
Does the order of operation that you place your LTRIM and RTRIM matter when used in conjunction with ISNULL? For instance, take the following example where a user potentially enters a bunch of spaces in a field, but we trim their input to be an actual NULL value to avoid storing empty strings.

I am performing the TRIM operations outside of ISNULL:

DECLARE @Test1 varchar(16) = '    '

IF LTRIM(RTRIM(ISNULL(@Test1,''))) = ''
BEGIN
    SET @Test1 = NULL
END

SELECT @Test1

This appropriately returns a true NULL value. Now let's place ISNULL on the outside:

DECLARE @Test2 varchar(16) = '    '

IF ISNULL(LTRIM(RTRIM(@Test2)),'') = ''
BEGIN
    SET @Test2 = NULL
END

SELECT @Test2

This also returns a NULL value. Both work well for the intended usage, but I'm curious if there is any difference to how the SQL query optimizer handles this?


texts: Automatically Execute Stored Procedure After Any RESTORE DATABASE Event

texts:
Is it possible to have SQL Server 2008 R2 Standard automatically execute a stored procedure in any given database that is restored or attachced to the instance?

I've got close to a solution by creating a server level trigger that executes a stored procedure in a given database after the DDL event CREATE_DATABASE or ALTER_DATABASE is fired. Unfortunately, this does not work for databack backup restores.

To elaborate, we have a 'clean up' stored procedure that exists in every database that we restore and I'm looking for a way to have this get executed automatically whenever a backup is restored to the instance.

Googling has pointed me to configuring either Audits or Policies in SQL Server to get this functionality, but these these features are quite overwhelming at first glance, so I can't tell if Audits or Polices are the avenue to start investigating.


texts: How to select records from two tables transactionally

texts:
I have table Messages that has fields, and another table called SentMessages, which has a one-to-one relationship to the Messages table.

Messages stores information about the message, including its text, and subject, and the recipient, etc.

create table Messages
(
    Id bigint not null identity(1, 1) primary key,
    Text nvarchar(max),
    Subject nvarchar(400)
    -- other fields
)

SentMessages stores information related to the transportation of the message to the clients.

create table SentMessages
(
    Id bigint not null primary key,
    SentOn datetime not null,
    DeliveredOn datetime null,
    -- other fields
)
go
alter table SentMessages with check add constrait [FK_SentMessages_Messages]
foreign key([Id])
references Messages ([Id])

They are designed to be separate, so please no advice on merging them.

Now the logic is to find all messages that are not sent yet, and send them. Basically a simple not in clause in a single-threaded scenario would do the trick:

select *
from Messages
where Id not in
(
    select Id
    from SentMessages
)

But this database is used by many concurrent threads, therefore it's possible for a message to be taken by more than one thread, hence be sent twice or even more.

If they were a single table, I could use update select statement to select unsent messages in a single transaction.

What options do I have though to make selection transactional, so that a message won't be sent twice?


texts:
Our application is collecting data points at different rates (between 500ms - 10,000ms) depending on user settings and that data is queried to plot points on a chart.

I started with the following table structure:

CREATE TABLE `monitored_parameter_data_378` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `command_id` int(11) DEFAULT NULL,
  `data_value` varchar(255) DEFAULT NULL,
  `system_unit` varchar(255) DEFAULT NULL,
  `unit` varchar(255) DEFAULT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Once we collected over 20,000 points (which will obviously keep growing) performance slowed down quite a bit.  It is taking between 950-1,100ms to get results back so I changed the schema to the following in hopes of benefitting from having the timestamp column indexed:

CREATE TABLE `monitored_parameter_data_378_test` (
  `command_id` int(11) DEFAULT NULL,
  `data_value` varchar(255) DEFAULT NULL,
  `system_unit` varchar(255) DEFAULT NULL,
  `unit` varchar(255) DEFAULT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `CREATED_IDX` (`created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Unfortunately I am not seeing any improvement in response time after making the change.

For testing and establishing a baseline I am using the following query:

SELECT * FROM <table_name> WHERE created BETWEEN '2017-01-01' AND '2017-03-13';

The indexed version of the table takes between 850ms-1,100ms to return so I am unsure of how best to proceed.  When I run EXPLAIN on the queries I see that the index is being used and that 50% of the data is filtered which is much better than the 11.11% in the non-indexed version.

Does anyone have any insight on how I might take performance to the next level given the fairly simple structure and approach to pulling the data?

Note: the machine in question is Raspberry Pi

Update: I marked @a_vlad's answer as accepted yesterday because based on the original question it is correct.  That said, I rewrote the logic to be done in 3 steps, first I get back the first/last point within the date range along with count of total points, second I calculate the interval between those points to based on the number of points the user wants to plot, last I query for the data by doing the following:

'SELECT * FROM monitored_parameter_data_378 WHERE id >= <variable containing first point in range> AND id <= <variable containing last point in range> AND id MOD <variable containing interval> = 0';

This gets the data back onto a chart in ~4 seconds in a table with 1,000,000 records.


texts:
I cannot find documentation about the usage of semi-colons in a select query, for example:

SELECT TEST.PARTIAL_PATH + ';' + G.ICON FROM GADGETS G
.... some inner joins...

Can somebody point to the documentation that gives some examples or try to explain it here?


texts: What is retrieved from disk during a query?

texts:
Fairly simple question, probably answered somewhere, but I can't seem to form the right search question for Google...

Do the number of columns in a particular table affect the performance of a query, when querying on a subset of that table?

For example, if table Foo has 20 columns, but my query only selects 5 of those columns, does having 20 (versus, say, 10) columns affect query performance? Assume for simplicity that anything in the WHERE clause is included in those 5 columns.

I'm concerned about the usage of Postgres' buffer cache in addition to the operating system's disk cache. I have a very lose understanding of Postgres' physical storage design. Tables are stored across several pages (defaulting to 8k in size per page), but I don't quite understand how tuples are arranged from there. Is P.G. smart enough to only fetch from disk the data that comprises those 5 columns?


texts:
I'm new to SQL and I'm trying to get my head round a lot of stuff but so far with little success. So I'm trying to get some help, here goes

I have a table "Userdata"

Email | FirstName | LastName | CreateDate
a@a.com | aLN | aLN | 01/01/2020
b@b.com | bLN | bLN | 03/01/2020
a@a.com | aLN | aLN | 04/01/2020

Basically I want to dedupe the column so that I only get the latest data in that table, so the result would be

Email | FirstName | LastName | CreateDate
b@b.com | bLN | bLN | 03/01/2020
a@a.com | aLN | aLN | 04/01/2020

I've tried several solutions with DISTINCT, GROUP BY, and JOINS but I can't seem to get this done. The problem I run into is that the table has dozens of columns and I would need to get the values of all of these columns.

SELECT DISTINCT t1.Email FROM "UserData" t1
INNER JOIN
(SELECT * FROM "UserData" t2 where t1.Email = t2.Email LIMIT 1) AS t2
ON t1.Email = t2.Email

This was my latest go at the problem but I get the error "multi-part identifier t1.Email could not be bound"


texts: Can't open the mysql.plugin table error when installing MySQL on Mac OS Sierra

texts:
I am attempting to install MySQL on macOS Sierra 10.12.5. I had a previous (non-functioning) install of MySQL so I followed the steps here to install a clean copy with Homebrew.

I had to create a var/log/mysql/error.log as it wasn't created automatically, however now it is logging I get the following error when I do a mysql.server start:

2017-07-24T13:36:28.554840Z 0 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2017-07-24T13:36:28.563492Z 0 [ERROR] unknown variable 'key_buffer=16M'
2017-07-24T13:36:28.563534Z 0 [ERROR] Aborting

This is how I initialised MySQL:

mysqld -initialize --verbose --user=`whoami` --basedir="$(brew --prefix mysql)" --datadir=/usr/local/var/mysql

When I start MySQL with homebrew I get no error:

CRMPiccos-MacBook:~ crmpicco$ brew services start mysql
==> Successfully started `mysql` (label: homebrew.mxcl.mysql)

Version info:

CRMPiccos-MacBook:log crmpicco$ mysql --version
mysql  Ver 14.14 Distrib 5.7.19, for osx10.12 (x86_64) using  EditLine wrapper


texts: SQL Server 2012: Users cannot see database properties

texts:
I migrated a database from SQL Server 2008 to 2012 and also the users.

Now the weird thing is that if the user connects to SSMS on the same server as the SQL Server engine, he can display database properties but he cannot when he uses SSMS to access it remotely (from another machine). He can log, run SQL queries but not display the properties.

Here is the error message:

Even stranger, if I log remotely with sa, I CAN display database properties but not as a normal user.

I looked around, tried many things, created new users in the new server. But same problem.

I tried this Rights needed to see database files in Properties dialog? in the Securables but the parameters do not seem to be saved. When I reopen they are gone - see screenshot

Please help